Sounds to me like your higher-ups come from a DB2 background. Older
versions of DB2 were infamous for their poor handling of foreign keys,
causing many shops to completely eliminate them from their DB designs.
Personally, I don't have a problem with removing the cascading updates and
deletes, but I can tell you from experience that trying to pick up a
project with no FKs / relationships defined is a nightmare. You're
guaranteeing that all maintenance done by people other than the DB
designers themselves will take at least twice as long. That means at least
twice as much money during the maintenance phase.
>From a developer's point of view, there is a huge benefit to being able to
see how things fit together, and unless they have specific business reasons
for denormalizing the data, the redundancy of removing the FKs will just
cause you more headaches than anything else.
--
Eric
_____________________
"Once the game is over, the King and the pawn go back in the same box."
-- Italian proverb
"Captain Palapa"
<[EMAIL PROTECTED] To: "SQL"
<[email protected]>
t> cc:
Subject: Cascading FKs in MSSQL
- Are they BAD?
05/20/2005 12:25
PM
Please respond to
sql
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?"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Purchase Captivate from House of Fusion, a Macromedia Authorized Affiliate and
support the CF community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=52
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2289
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=89.70.6
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
