Keeping referential integrity across multiple databases

Question

What are the best practices for keeping referential integrity across multiple databases? since there's no built-in functions

Or is it better to partition a single database?

Update See kevin's example below. that is my situation. My inventory database has tables that reference the employeeId in the employees database.

These databases are currently maintained at different locations (Different servers)

Answer 1

I guess it depends on your needs, but I'd probably lean towards a single database. To do it across multiplbe databases you need distributed transactions support.

Answer 2

Definitely better to partitition to a single DB. If you needed to do it triggers would help (yuck). Can't you just partition the DB by using schemas instead (the AdventureWorks sample DB is an example)?

Answer 3

With 2k05/2k08 it's definitely better to partition a single database. You have all the benefits of storing data like it is in multiple databases while being able to use the functions of a single database, like foreign keys.

That being said, you shouldn't keep everything in a single database. Logically when groups tables don't fit together, I normally separate them into their own databases. For example, I wouldn't necessarily combine place an orders system database and the employee management database together. I suppose there could be reasons to do so, but I am sure you get my point of logically separating data stores where appropriate.

What you should look at is how much the two databases interact. If there are lots of fields which would join across databases, then I would say that it is probably a good idea. If it's maybe one or two fields linking to the employee table, then it might not be worth doing. Your other option is, if the number of joins are small, is to duplicate the necessary tables into the inventory database, especially if it is one table and the two existing databases are large and rather complex.

Answer 4

You could use triggers to do the job but....
I am afraid that you will spend an enormous amount of time making sure that everything works. A single database with some partitioned tables may be better. Do you actually need multiple databases or just one database with multiple filegroups spread over physical devices? You may consider that option too. Matej

Answer 5

I'd rather have one database. Explained here:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/13/be-aware-of-these-loopholes-in-your-referential-integrity.aspx