SQL design around lack of cross-database foreign k

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

I wonder if SQL Server has a feature like Oracle's materialized views? This is an object that you define with a query like a view, but the results of the query get stored as a table. There are then various mechanisms for automatically refreshing.

If there is such a feature, I would suggest making a materialized view of the core table(s) in each satellite database. Then you can reference that in your foreign keys. The main issue would be whether it can be refreshed frequently enough for your needs.

Answer 2

We have such a modularity in our products, but our database requirements are merged together during installtion. For example our admin package and product A may be the initial purchase by a client where they install the two modules into database X. If they later buy product B the database component is layered right on top of database X adding in the DRI where necessary.

The only case where I have seen the need for separate databases from a design perspective is when you are drawing a hard line between business units (such as a corporation) at which point the issue is really a type of partitioning. Great Plains Dynamics does this where they have a single administrative database, and multiple corporation databases. However each module in GP for a given corporation resides in that single database.

Of course if you are stuck with separate databases, I would agree that D is the best option.

Answer 3

You should implement a Service Oriented Architecture. Where the different services in the system are running with their on database schema. Then let you applications run independently from any databases but let them run against the services.