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).
Best Answer
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.