August 2, 2006 at 6:40 pm
Good evening all. I have a question. I am architecting a system that has a handful of databases. One such situation here is that it is going to be a hosted solution whereby each client will have their own DB. To that end there are of course lookup tables involved. I would like to centralize most / all lookup tables in one database and have each client database rely on / check that location.
Being that foreign keys can not be bound across DB's I was wondering if an insert trigger, providing the constraint would be the best practice here?
The other DB's are on the same server / SQL instance.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
August 3, 2006 at 2:21 am
The question may more be if having those lookuptables in a 'central' db is the best solution instead of having each db as a separate independent entity.
But for cross-db references, then yes, a trigger is your only choice.
/Kenneth
August 3, 2006 at 7:50 am
Thank you for your reply. Yes I have considered the option of having these in each database.
In cases where the client can edit / add to the list I have done this. In many cases however the lookup table is something that we will maintain and a central location; non-replicated data seems to make the most sense.
There is the question of performance and I am wondering how much impact a deployment such as I have suggested would have?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
August 4, 2006 at 3:16 am
I'd be afraid to implement it this way, also because of performance... I would suggest the same as Kenneth - the tables should be in each client database. If you want to manage these tables, give users read permissions only and think about how to maintain the tables centrally.
If you have the lookup table in another DB, you'll have to access it repeatedly and very often... that's a big difference as opposed to transferring data between databases only when the lookup table in central DB is changed.
August 4, 2006 at 1:56 pm
I had a project with a similar requirement. We used replication to reference the common database into each of the client databases. Then FK constraints could be set up in each. Of course, we had to be careful when adding a new datum to the reference database (often had to set up a wait for the replicaiton to take place.)
August 4, 2006 at 2:52 pm
Hmmm... Interesting. Another idea on this is to have a job that would look at the lookup tables and update the individual databases with new records. I COULD even do this in looking at Systobjects and create the lookup tables as new tables are added to the central database; looking at my naming convention L_xxx (for lookup tables). This actually gives me a few cool ideas that I want to try. The process can be automated without sacrificing performance.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
August 7, 2006 at 1:32 am
Don't forget the flip side of the coin, performance isnt' all. Integrity is even more important.
Consider that when you've split what logically belongs into a single db into more than one db's, how will you deal with backups and recovery?
If the 'central' db has to be restored, will that affect all other db's as well, or maybe only a few? Then which ones? And also the other way around, will a single user db being restored affect the 'central' db? How can you be sure that when restores are done, that integrity also is preserved? These are things that also has to be considered, besides the performance.
/Kenneth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply