Tom, I'm interested in hearing what others have to ay about this also. I'm involved in a project now where another dba has decided to create a separate db for the lookup tables. He's migrated it from Access to SQL Server 2000 at the same time. However, I've taken a look at the relationships and the triggers and there's nothing there. So referential integrity (RI) at this point seems to have been abandoned.
What stood out in your post to me was "These tables are relatively static and change very infrequently." That statement leads me to think having them in each db is the way to go, given that RI can be enforced w/o triggers, query performance won't suffer, and the values in the tables don't change much. Some dbas prefer to use triggers to enforce RI even with a single database (i.e., no cross database issues). Most things I've read suggest using declarative RI instead of triggers. That's what I usually do. However, there are advantages and disadvantages to each. Peter >I have database question that I am sure many CF developers faced before (as >I did) and I was wondering which way turned better in the end for them (I >only experimented with one). > >There are certain tables, for example "state", "country" and "cityList" that >are common to many web applications. These tables are relatively static and >change very infrequently. Should every web application have their own copy >of the data which is synchronized with other copies via triggers or >manually? Or should one have a special schema where all these tables are in? >There are advantages and disadvantages to both ways. > >If SQL DBs were object oriented one would use single "static master DB" and >have children DBs use its tables. But RDBMS are not object oriented >(relational, not object). SQL Server 2000 doesn't allow for cross DB foreign >key references thus triggers would have to be implemented on "static master >DB" to update every child database key reference on a change to "static >master DB" data. Also, every child would have to have a trigger on update >and insert to check against the "static master DB" whatever keys are valid. >Moreover, many queries of child table data would require going over to >master DB - cross DB linking could be a bad idea in terms of physical DB >design, the "static master DB" data could be highly contended for. The >advantage is rather obvious - one single copy of data. > >Now scenario two, much more common, every DB has its own copy. The problem >here, as many people know and which I am trying to avoid, is heavy data >duplication. As data changes in one table, we need to ensure so does it >change in other tables. As above mentioned, we need to either use triggers >or do some manual labor. Advantages here are that tables that are in the >current database can be used as part of foreign constraints in other tables. >They can also be more customized. The overall design is simpler, you don't >have to worry about some "foreign" tables. Data access may also be faster, >since tables are local and everyone is not fighting for them > >After I wrote it all down, I am leaning towards more common approach - every >DB has its own copy with one DB copy serving as "master copy". What do you >guys think? > >TK ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:242105 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
