On Wed, March 18, 2009 06:29, Johnny Withers wrote:
> Everytime we make a schema change in development we save an SQL script
> a directory named in MMDDYYYY format. The scripts are named in
> MMDDYYYY_NNN_DESCRIPTION.SQL format, where NNN is the sequence number.
I realize its probably a bit late for this, but wouldn't YYYYMMDD... make
more sense? That way they would sort in proper order.
> allows us to string the files together in the proper order to apply when
> update goes out. We simply look at the last update date, combine all the
> scripts (either using cmd line utilties or an app we wrote to do it) and
> apply this combined script to all the databases. The application to all
> databases is currently handled by hand each time since we don't more than
> once a week and there are not a lot of databases (15 to 20 right now).
> I can see us needing an automated way to apply these scripts in the future
> and it'll probably involve a custom application that makes use of a
> file that has the connection string for each database. It'd simply loop
> these connections and apply the scripts over a date range.
> We don't have a need for being able to query multiple databases at a time.
> Since some of these databases reside on different servers, I don't even
> think we could do that -- unless we started using an engine that supports
> On Wed, Mar 18, 2009 at 7:01 AM, Stephen Sunderlin
>> How do do you synchronize alter schema across the databases and what
>> do you use for internal reporting aggregate across the databases?
>> Mar 18, 2009 06:47:52 AM, johnny@stripped wrote:
>> I have an application and database doing this right now. We run both
>> ways, a single db per customer and a single customer per db. Smaller
>> customers are in a shared enviroment and larger customers have their
>> own db, sometimes they even have their own server. We use a company_id
>> field in each table to identify the data.
>> On 3/17/09, Arthur Fuller <fuller.artful@stripped> wrote:
>> > Are these databases identical or merely similar? If they are
>> > identical, I'd go for one database per customer. Then you have
>> > easy structure updates and above all, consistent front-end code, in
>> > whatever language that occurs. Just obtain the customer ID and then
>> > appropriate database. Everything else can remain the same.
>> > The only fly in the ointment concerns whether you'd ever have the
>> > need to cross databases. I would imagine that sort of thing is for
>> > use, not the customers. In that case, the performance hit if any won't
>> > impact upon the customer, just you.
>> > hth,
>> > Arthur
>> > On Tue, Mar 17, 2009 at 8:21 PM, Daevid Vincent <daevid@stripped>
>> >> I'm writing a report tool wherein we have many customers who
>> >> to this SaaS. There are millions of rows of data per customer. All
>> >> customers are islands from each other (of course).
>> >> Are there any major issues or benefits between storing each customer
>> >> their own database (with their own tables), or all lumped into a
>> >> database?
>> >> At first thought, it seems that by separating them, queries should be
>> >> faster no (as there is less data to sift though per customer)? It of
>> >> course makes upgrading table schema a wee bit more cumbersome, but a
>> >> simple loop and script can handle that easily enough. And since you
>> >> query across databases, we can still make internal aggregate reports
>> >> our own usage.
>> >> For example: SELECT * FROM customerA.foo.bar JOIN customerB.foo.bar;
>> >> we can use UNIONS etc. too.
>> >> Consolidating them into one would seem to bloat the tables and slow
>> >> things down (or is the fact that mySQL uses B-Trees invalidate that
>> >> theory)? It also makes us have to have a customer_id entry in every
>> >> table basically (or some FK to distinguish who's data is who's). It
>> >> feels like it could leak data if a malformed query were to get
>> >> although I'm not terribly worried about this as we do some heavy UAT
>> >> before pushing from DEV to TEST to PROD.
>> >> Performance is a major factor concern here given our huge data sets
>> >> involved. Does joining across databases impose any speed/performance
>> >> hits vs. just joining across tables within a single database?
>> >> http://daevid.com
>> Johnny Withers
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
> Johnny Withers