Hey, all. I've been struggling with this issue for about a week. The higher ups had decided that my database model, complete with foreign keys that would handle cascading deletes and update (where necessary) were to be removed from the design. I felt this was a bit off, and questioned why. I was told that it was because we don't use them, mostly due to processor overhead on the db server. Here were some of the points I gathered to prepare to make my case against not having them and having to issue the cascades through ColdFusion code instead. (I have since lost this case and had to strip all the referential integrity (FK's) out of the design and resulting SQL script.) ---------------------------------------------------------- .. Inability to access / modify data through other applications .. Highly increased potential of orphaned records .. Increased potential of incorrect reporting due to orphans .. Deletes / Updates handled in multiple locations must have redundant code, often different .. Orphans will occur, period, if developers are relied on to maintain integrity through front-end code. Many developers interact with previous database design and structure they themselves had no involvement in creating. .. For all that we are asked to NOT do ~SELECT * FROM.~ when not absolutely necessary to reduce bandwidth, this also will increase bandwidth, though not to the same degree. .. It seems irresponsible to ignore the tools that are built into the database when they are so easy to use, relieving developers of that extra burden and time and responsibility. Microsoft thought it important to add the cascade capability to foreign keys in SQL2000 rather than relying on triggers. (I've always waited for this feature in MSSQL, anyway!) ---------------------------------------------------------- New points I came up with today... ---------------------------------------------------------- .. Being told that these FKs require more CPU overhead on the database server, it would seem to me that it would be no more than issuing separate DELETE statements from the application, including sending script to the database engine, additional transactions, larger log files, etc? (Granted, I'm not certain about these, as I can't say I'm overly familiar with the results in those areas from issuing many delete statements from the application code -- I've always done it RIGHT!) .. Also in rebuttal to the above, isn't this is what is meant by a ~Relational Database~, that records are ~related~ and should be managed as logical units? ---------------------------------------------------------- I'd just like to know other folks thoughts on this issue. I would like to see this through and to BE RIGHT about this. But making a case can be tough when you're the low man on the totem pole. Thanks,
CaptainPalapa "Why ain't I in Cozumel right now?" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2286 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
