For better or worse, you will find there's solution that depends on multiple databases that reference a typical administration database. Databases ship included in modules, and never all modules are needed to have an installation (most likely why we now have multiple databases to begin with). The admin database is needed, however ... so most commonly it is there.

I must bring some referential integrity and order towards the chaos, but am stymied by SQL server's lack of ability to complete mix-database foreign secrets. There's Very little churn within the database, but information is going to be placed/up-to-date by (ahem) non-technical customers.

My options when i discover their whereabouts are:

a) Impose pseudo foreign key using triggers (ok, but a little of labor)

b) Use triggers to duplicate from admin with other databases (a obvious occur)

c) Impose psuedo foreign type in code / DAL (doesn't play well with ORM)

d) Don't be concerned about this at DB level, use good UI design to make certain nobody does anything stupid and restrict access/hold breath on direct SQL access.

Frankly, I am inclined to choose "D", but figured I'd venture out for opinions wiser than me ...

Presuming each module is to establish to ensure that it's related to the administration database, you may have the ability to simplify factor by establishing sights for that administration tables within each module database.

We now have the identical problem and to be honest, it sucks. Our only solution we found effective was option D and taking advantage of the company layer to keep things synchronized (encasing in transactions etc.)

We now have this type of modularity within our items, but our database needs get together throughout installtion. For instance our admin package and product A might be the first purchase with a client where they install the 2 modules into database X. When they later buy product B the database component is layered directly on surface of database X including the DRI where necessary.

The only real situation where I've come across the requirement for separate databases from the design perspective is when you're drawing a tough line between business models (like a corporation) after which the problem can be a kind of partitioning. Great Flatlands Dynamics performs this where there is a single administrative database, and multiple corporation databases. However each module in GP for any given corporation resides for the reason that single database.

Obviously if you're tied to separate databases, I'd agree that D is the greatest option.

Based on your database implementation, frequently you'll have the ability to link tables from another database (the AdminDB) and also have them come in your various module databases.

In Microsoft Access you are able to link tables by right clicking, after which selecting an ODBC databases. In Oracle they refer to it as a database link. I am prepared to wager SQLServer has some type of this implementation lacking applying custom replication on one table.

When you link inside your foreign admin tables for your module databases (or the other way around), then you definitely should have the ability to define constraints as though the tables were inside the same schema.

Another option might be even simpler. Let's say you used exactly the same schema for those modules and admin database? You realize the admin database exists, so simply run the table creation script against that schema. As lengthy as there's no table / view / saved procedure naming conflicts, it really should work simply by altering the dblogin in most modules to complement.