sql server - SQL design around lack of cross-database foreign key references

Question

For better or worse, we have a solution that relies on multiple databases that all reference a common administration database. Databases ship as part of modules, and not all modules are required for an installation (probably why we have multiple databases in the first place). The admin database is required, however ... so it will always be there.

I would like to bring some referential integrity and order to the chaos, but am stymied by SQL server's inability to do cross-database foreign keys. There is NOT a lot of churn in the database, but information will be inserted/updated by (ahem) non-technical users.

My choices as I see them are:

a) Impose pseudo foreign key using triggers (ok, but a bit of work)

b) Use triggers to replicate from admin to other databases (a clear recipe for disaster)

c) Impose psuedo foreign key in code / DAL (does not play well with ORM)

d) Don't worry about it at DB level, use good UI design to make sure no one does anything stupid and restrict access/hold breath on direct SQL access.

Frankly, I'm inclined to go with "D", but figured I'd go out for opinions smarter than me ...

Answer 1

We have the exact same problem and quite frankly, it sucks. Our only solution we found effective was option D and using the business layer to try and keep things in sync (encasing in transactions etc.)