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:242004 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
