House of Fusion
Search over 2,500 ColdFusion resources here
  
Home of the ColdFusion Community

Mailing Lists
Home /  Groups /  ColdFusion Talk (CF-Talk)

Cross referential database integrity

  << Previous Post |  RSS |  Sort Oldest First |  Sort Latest First |  Subscribe to this Group Next >> 
Tom,
Peter Legg
06/02/06 03:00 P
Peter,
Tom Kitta
06/02/06 05:25 P
Tom Kitta wrote:
Jochem van Dieten
06/04/06 02:21 P
Munson, Jacob wrote:
Jochem van Dieten
06/05/06 05:12 P
Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Tom Kitta
06/02/2006 12:07 AM

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

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Peter Legg
06/02/2006 03:00 PM

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 ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Tom Kitta
06/02/2006 05:25 PM

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 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 ----- Excess quoted text cut - see Original Post for more ----- 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

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Denny Valliant
06/02/2006 07:57 PM

Out of curiosity, is this a good example of where you'd use federated tables maybe, if'n yer using mysql? :d ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Tom Kitta
06/04/2006 01:57 PM

Note sure when you should use federated tables as far as MySQL and DB2 are concerned - didn't use anything but simple MySQL and never used DB2. For most part its MS SQL and Oracle. Maybe someone else knows usage criteria for federated tables? TK Out of curiosity, is this a good example of where you'd use federated tables maybe, if'n yer using mysql? :d ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Jochem van Dieten
06/04/2006 02:21 PM

Tom Kitta wrote: > Note sure when you should use federated tables as far as MySQL and DB2 are > concerned - didn't use anything but simple MySQL and never used DB2. For > most part its MS SQL and Oracle. > > Maybe someone else knows usage criteria for federated tables? Don't use the MySQL version for this: they don't support transactions so they don't support referential integrity. Jochem

Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Dave Watts
06/02/2006 05:30 PM

----- Excess quoted text cut - see Original Post for more ----- I run into this somewhat often with DBAs who learned on very, very old mainframe databases, where declarative referential integrity wasn't an option. It is sometimes difficult to convince them why DRI is better, believe it or not. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information!

Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Munson, Jacob
06/05/2006 05:02 PM

> Don't use the MySQL version for this: they don't support > transactions so > they don't support referential integrity. MySQL supports transactions if you use the right storage engine (like InnoDB): http://dev.mysql.com/books/mysqlpress/mysql-tutorial/ch10.html InnoDB recently got purchased by Oracle, so MySQL is working on their own storage engine to address this issue.  Oracle hasn't done anything adversarial, yet, but I can't say I blame MySQL AB for going to plan B. ----------- This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. A1.

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Jochem van Dieten
06/05/2006 05:12 PM

Munson, Jacob wrote: ----- Excess quoted text cut - see Original Post for more ----- The storage engine under discussion was specifically the engine for federated tables. It does not support transactions. > InnoDB recently got purchased by Oracle, so MySQL is working on their > own storage engine to address this issue.  Oracle hasn't done anything > adversarial, yet, but I can't say I blame MySQL AB for going to plan B. Plan D. Plan A was to convince the world transactions are evil, plan B was InnoDB, plan C was BDB and now they are at plan D. Jochem


<< Previous Thread Today's Threads Next Thread >>

Search cf-talk

July 23, 2014

<<   <   Today   >   >>
Su Mo Tu We Th Fr Sa
     1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31     

Designer, Developer and mobile workflow conference