Peter, Yes, referential integrity is frequently forgotten by people who design DBs, don't know why, maybe its sloppiness or they want to be hired later on to "clean up" the database?
Since the tables don't change often I am planning to have a copy of them in a every child database. Later on triggers can be created on both child and master copies that would accommodate data updates and insertions. RI for the child tables that link to data in their own DB can be enforced using foreign key constraints. Probably best understood with example table "states", placed in DB "company". Another DB, "marketing" will also have table "states" that is a copy of the table in "company" DB. Other tables in "marketing" DB will have foreign keys in local copy of "states" table. In case a state name changes (Quebec province code changed few years ago) then the table "states" in "company" DB gets updated - if we want to the change can be reflected in its copy in "marketing" DB automatically via triggers. As for DBAs using triggers for RI on local DB tables - this is old school, not needed for something like 10 years with large DBs (don't know about MySQL & 10 years). As mentioned by you, declarative RI is the way to go. TK -----Original Message----- From: Peter Legg [mailto:[EMAIL PROTECTED] Sent: Friday, June 02, 2006 2:59 PM To: CF-Talk Subject: Re: Cross referential database integrity 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:242149 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
