I thought I would easily find a discussion on the internet about this subject but I didn't find anything to really help me.
I have many applications that need to share common tables. Think about an Employee table for example. I don't want to have this table duplicated in each project's database!
With the few information that I've found here and there on this subject, I read that:
- doing cross database views on the common table is not really a good practice and must be avoided if possible
- problem with the referential integrity, must go with triggers but it doesn't solve everything
- if the common database fail, we can have problems to restore backups and be sure that we are in-sync with the other databases
- probably other problems too...
- duplicating the shared tables across all the databases will need to be synced. More maintenance, more disk spaced used, bigger backup, etc
- only use ONE database but with different schemes for my projects
So, I guest that I'm not the first one that need to have a common tables for multiple projects.
- What is the best practice that I must follow?
- Is there articles or books I can read on this subject?
Thank you for your help!
sql server 2012
March 7, 2013 1:24 AM
March 7, 2013 2:37 PM