1

Possible Duplicate:
Single or multiple databases

We are developing an application that will have application-wide data and client-owned data that will ideally reside in a database that will be specific to/owned by each client.

So we are confronted by the issue of whether to:

  • replicate the application-wide data across all client-owned databases
  • keep the application-wide data in a separate 'meta' database and rely mainly on the application level to coordinate between meta and client database data
  • keep the application-wide data in a separate 'meta' database and utilize cross-database foreign keys and joins to make the meta database effectively a part of all client databases

I am leaning towards the third option, excited about the flexibility and normalization it could provide. But i don't consider myself an experienced MySQL user, and i'm not sure about the performance, security, and administration ramifications.

Any experience/guidance would be much appreciated!

Community
  • 1
  • 1
ilasno
  • 714
  • 1
  • 13
  • 31
  • @OMG Ponies I appreciate the link to [Single or multiple databases](http://stackoverflow.com/questions/1676552/single-or-multiple-databases), but i do think that the ability to define foreign keys and join between databases in queries would affect the decision.. – ilasno May 06 '11 at 22:33
  • JOIN syntax (ANSI-89 and 92) supported everywhere, MySQL MyISAM is the only engine that doesn't support foreign keys (so you'd be unlikely to use) -- the foreign key syntax may differ, but otherwise foreign key constraints are found on nearly every database I can think of. What you belief has no bearing on reality. – OMG Ponies May 06 '11 at 22:47
  • My bad, i apologize for my ignorance. – ilasno May 07 '11 at 20:01

2 Answers2

1

Keep the application-wide data in a separate 'meta' database and rely mainly on the application level to coordinate between meta and client database data.

Worked for us.

Don't replicate, it's a nightmare.

Avoid cross-database foreign keys and joins since they're not very portable. At some point you will want to migrate to another database. Pure data migrates very cleanly.

When we did platform upgrades (OS, DB versions, etc.) we extracted the data from the "meta" and client-specific databases on one server, and loaded it into the new server. Up and running in seconds in the new configuration. No mysteries about getting cross database keys and joins to work.

S.Lott
  • 384,516
  • 81
  • 508
  • 779
1

One important factor to consider is whether the clients will be on independent upgrade paths for the application. If they will, then almost certainly they will need their own local copy of the application-wide data in order to avoid compatibility problems on upgrade.

Another factor will be whether the clients will have reliable connectivity to a central server that provides application-wide data. If not, they should have their own copy of the data to avoid downtime risk.

I think the third option you present seems most risky in terms of performance and reliability, unless the "local" client databases and the central app database are on the same machine.

Alanyst
  • 1,390
  • 7
  • 10
  • The upgrade paths is a great point; i guess with shared application-wide data any upgrades to that part of the system would be universal. Initially the client and central dbs will be on the same machine, but there is definitely the plan for remote 'dedicated' systems. The thought was that those would have their own local version of the application-wide data (and then we're back to replication). Thanks, appreciate your time! – ilasno May 06 '11 at 22:39