How do I store data that is shared between databases?

How do I store data that is shared between databases?

Suppose a database for a contact management system. Each user is given a separate database. User can store his/her contacts' education information.

Currently there's a table called School in every database where the name of every school in the country is stored. School table is referenced as a FK by Contact table.

School table gets updated every year or so, as new schools开发者_如何学Go get added or existing schools change names.

As the school information is common across all user databases, moving it into a separate common database seems to be a better idea. but when it's moved to a separate database, you can not create a FK constraint between School and Contact.

What is the best practice for this kind of situation?

(p.s. I'm using SQL Server if that is relevant)

Things to consider

• Database is a unit of backup/restore.
• It may not be possible to restore two databases to the same point in time.
• Foreign keys are not supported across databases.

Hence, I would suggest managing the School -- and any other common table -- in one reference DB and then replicating those tables to other DBs.

Just straight out of the box, foreign key constraints aren't going to help you. You could look into replicating the individual schools table.

Based on the fact that you won't query tables with the SchoolID column very often I'll asume that inserts/updates to these tables will be really rare... In this case you could create a constraint on the table in which you need the FKs that checks for the existence of such SchoolID in the Schools table.

Note that every insert/update to the table with the SchoolID column will literally perform a query to another DB so, distance between databases, the way they connect to each other and many other factors may impact the performance of the insert/update statements.

Still, if they're on the same server and you have your indexes and primary keys all set up, the query should be fairly fast.