Menu
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 ...
634
You could instead configure two tables such that one refers to the other, but without any defined foreign key. For instance, let's say you have a Customer table with its own ID column, and you have a User table with its own ID as well. You could add a UserId column to Customer and still use it for querying purposes.
The obvious problem with the lack of foreign keys is that a database can't enforce referential integrity and if it wasn't taken care of properly at the higher level then this might lead to inconsistent data (child rows without corresponding parent rows).
When you drop a foreign key using the DROP FOREIGN KEY clause of the ALTER TABLE statement, Db2 drops the corresponding referential relationships. (You must have the ALTER privilege on the dependent table and either the ALTER or REFERENCES privilege on the parent table.)
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.)
144
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
