5

Database design: one database or multiple databases, which is best?

We have a database which has about a 100 or so tables, accessed by about five different applications. Five different applications have their own set of tables but also need to access about 20 master tables (used by all our systems: users, accounts, contacts, shops, etc). Now we are going to have another 15 or so applications with their own set of tables but also again need access to get information from the master tables. So before we get set up what do you think is the best schema and database set up. i.e. one database with all applications including the master ones. Each application has its own database with the master records staying in master?

Anyone's thoughts here would be much appreciated. I think I am leaning towards separate databases so they can be managed better, and performance should be better (maybe not?).

If I go with separate are their any implications: setting up references wont be possible, performance joining databases for selects, updates, asp.net needs 2 connections strings (is that even possible with say entity framework database first or LINQ DBML).

Improve this question
8
  • 1
    The most important thing (IMO) to consider is where the transaction/recovery boundaries lie - if one application needs to have its data rolled back/recovered from backup, does that imply that every application should similarly be rolled back? If it does, they should be in the same db. If it doesn't then separate DBs may be indicated. – Damien_The_Unbeliever May 13 '13 at 9:12
  • hadnt even thought about that oops. They would be independent applications and therefore could be rolled back separately. – Jon May 13 '13 at 9:34
  • what about database set up: is it good practice to access a master db tables from other db's. Or maybe i should copy master tables to all other db's, but this would then have out of date data until tables are copied again IE nightly feed. any suggestions? – Jon May 13 '13 at 12:20
  • I flagged for redirect to dba.stackexchange, since I thought your question may be a better fit for there. If you want the master tables present in each application database, then you can look into the various replication options that SQL Server supports. There's nothing to stop you have a mix of replicated and non-replicated tables inside the same database. This may be beneficial to setup now if you expect that you may outgrow a single instance and want to split databases onto different servers. – Damien_The_Unbeliever May 13 '13 at 13:12
  • ok thanks. the more help and information i get on this the better. would like to get it refactored now before we have the next 15 applications done. – Jon May 13 '13 at 16:37
7

I would lean toward multiple databases. Primarily because you can set them up for different recovery, SLA, maintenance, deployment, changes, etc. And also this allows you to move certain databases to faster or larger I/O on different drives without disrupting everyone.

For the master tables that are used by all the applications, no I don't think you should have multiple copies of these. Your applications can query different databases and/or have different connection strings for different tasks (best separated by a middle tier of some sort in most cases). If the app-specific code within a single database needs to have some kind of reference to them, you can use synonyms or three-part names. This is easy to extend if you later need to split out not just across databases but across multiple instances / servers (just add a linked server and make it a four-part name).

Only if the link between these servers is notoriously slow or otherwise unreliable would I consider making replicas of the master tables. I talked about how to do that here and here, even though I don't explicitly cover moving data across servers.

Improve this answer
6
  • Thanks for the reply. The master tables will need to be queried frequently and have the ability to use joins etc. I will do some more investigating today into the split and return later with any questions i have. thanks again. – Jonnymaboy May 14 '13 at 7:46
  • Aaron, Sorry for the delay in posting back. Based on my above comment do you still agree to keep the master db seperate and just reference/ joint to it using synonyms?. Do you know if this is this possible using Entity Framework 5 to have synomyms and 2 databases. I have looked here geekswithblogs.net/michaelalisonalviar/archive/2012/09/08/… but think its an old article. Thanks. – Jonnymaboy Jun 11 '13 at 10:56
  • Yes, however I know very little about EF except that it is inflexible and tends to make plausible solutions implausible. – Aaron Bertrand Jun 11 '13 at 11:12
  • OK thanks again. I will en-devour to research synonyms in more detail, and there options within EF. thanks Jon – Jonnymaboy Jun 11 '13 at 12:35
  • but we have change 4 DBs in Project server 2010 and why Microsoft convert them to one database in project server 2013? – saber tabatabaee yazdi Oct 31 '16 at 6:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.