Best practice when using a shared table across multiple databases

5 pts.
Tags:
SQL Server
SQL Server 2012
Hi everyone!
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!
Alex


Software/Hardware used:
sql server 2012

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following