Enforcing Referential integrity in Microsoft SQL Server 2000
-
Comments posted to this topic are about the content posted at http://www.sqlserve
> Nick Duckstein -
Nick,
As much as I agree with your general point that integrity should be enforced at the data layer, not the application etc... I think you shot yourself in the foot when you advocated the use of what I call Massively Unified Code-Key (MUCK) tables.
The number of tables in a "schema" isn't what complicates things, it is much more often the inappropriate combining of things into fewer tables that complicates both enforcement of integrity constraints and querying. This is the whole purpose of normalization, eliminate dependencies in your tables so that integrity CAN be enforced through the use of Primary and Foreign Keys. See my article on the subject for a more detaied treatment of the subject.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Nick,
You forget a key problem with the OTLT (One True Lookup Table), what dcpeterson calls a "MUCK" table -- It very clearly violates the First Normal Form, destroying all data integrity. RI is not the only means of enforcing integrity in an RDBMS. Domain integrity is just as important.
--
Adam Machanic
whoisactive -
Nick - I very much agree with Don Peterson's comment - a very good article except for the suggestion to consolidate lookup tables. Don's article, for which he provides a link, give an excellent summary of the issue and provides good reasons for *not* going the route of consolidating lookup tables.
It's not that lookup tables complicate a database - it's that life and business processes can in fact be complicated, and accurately representing the facts and the relationships between them properly leads to a lot of "little tables."
That one criticism notwithstanding, I liked your article and your writing style.
Best regards,
SteveR
-
Deal with some old DBase application that don't (cannot) enforce referential integrity and you know why you should always use them. Even today there are modern samples of this and usually they just become inconsistent and getting good data out of them is a real pain.
Still I don't know why you don't stick to the subject of Referential integrity and get to using MUCK (or true-lookup-tables). They can become a real pain and they provide no benefit, except a good key to integrity problems. I've designed some lookup tables and the database had almost an equal number of lookup tables compared to other tables. In the end adding the lookup tables paid off because:
- A slight addition was needed that the lookup table could provide, not much changes to meet the new requirement.
- Adding some more bits and bytes with predefined condities to the lookup table made my filtering work much easier.
The first thing can be done with a MUCK table, the second one cannot. Besided, why complicate a design with such tables?
Even in OO you don't do what you do with MUCK tables, you only take benefit that the object has the same layout (so why not use the same class or definition(!)), but you don't put them in the same array, unless you are, IMHO, some kind of idiot.
It might confuse people that tables are NOT the same as classes, rather we should change SQL to:
CREATE DEFINITION Lookup (Id, Value)
CREATE TABLE CustomerStatus FOLLOWING Lookup
CREATE TABLE BatchStatus FOLLOWING Lookup
CREATE TABLE ... FOLLOWING Lookup
CREATE DEFINITION OrderLookup : Lookup (canceled bit)
CREATE TABLE OrderStatus FOLLOWING OrderLookup
Now we are going the correct way, view definition as class and table as array of a definition. But who's going to build it?
-
In line with the previous replies. The lookup tables are (should be) DIFFERENT Entities. The business purpose of the model CAN change therefore the ablity to change will be crippled by using the proposed "UNIFIED" approach.
On another point though the article seems well organized and makes a good sense on the reasons for RI.
HTH\
* Noel -
Also in agreement with the comments regarding MUCK tables. This has cropped up in various articles on the site, there really should be some moderation and/or editing process to stop anymore appearing. The more often its mentioned the more likely it is that someone will assume its a good practice, which we all know it most definately isn't.
-
In general I agree with this article but there is one massive BUT that has been overlooked here and that is performance.
I have a database where the design of the database with its referential integrity is such that any attempt to archive off old data means taking the server off-line for a day. Performance is totally unacceptable for deleting data.
I am going to write up my findings on the performance issues with DRI so there is something quantitive rather than emotive to mull over
-
David:
Are you talking about DRI, or "cascading" actions? Note that DRI is only primary key/foreign key constraints. The cascading stuff is not necessary for enforcing any kind of RI, and is only there to make life easier in some cases. If it's too slow, turn it off and write your own scripts.
If it's not cascading actions you're referring to, I'd be interested in why your deletes are so slow that they require taking the server offline. Sounds like you're missing some key indexes to support those deletes?
--
Adam Machanic
whoisactive -
I'm not talking about the infestation known to MS Access programmers as cascading.
I have inherrited a database whose lookup tables would give DCPeterson apoplexy. The design was done by C++ programmers so the reasoning is that a lookup table consists of an id and description, this would be an implementation of a single class therefore this represents an implementation of a single table.
Beyond the lookup tables this also means that other "classes" are also given their own tables when perhaps they shouldn't be. Consequently there are foreign keys all over the place.
There are over 200 tables in the database all cross linked to each other and to the lookup table.
One delete requires a delete from several other tables all of which get stung by DRI.
Basically the database needs scrapping and rewriting but
a) There are several very large business critical applications that interact with this database.
b) The database has to be available 24/7/363 (Yes I mean 363)
c) The redesign would take over one year even before recoding the apps
-
Okay, so you're saying that some OO devs designed this database like they would design an OO system? I've seen some absolutely --wonderful-- output from those situations. Confusion about is-a vs. has-a relationships and how the DBMS enforces such relationships. In one case, an online ordering (ecommerce) system was designed such that the products table had a foreign key to the line items table! End result: These guys were inserting fake orders every time they wanted to sell new products. And of course, there were duplicate products inserted with every order. Luckily, their volume was very low
--
Adam Machanic
whoisactive -
David: I would hope that I haven't built quite that bad of a reputation for apoplexia!
I admit that I do tend to respond to certain topics rather forcefully, but that is because I've seen too many systems just like the one you and Adam describe, and it annoys the hell out of me when I have to clean up the mess. I have often likened a job like that to trying to separate the toilet paper out of the sewage; at best it's a nasty, difficult job, at worst it's a nasty difficult job that also happens to be impossible. Then to hear (or read) someone put forth this kind of "solution" as "elegant" just makes me want to wring their neck...
To anyone reading this: Relations (db tables) <> OO Objects!!!!! Don't make that mistake, they are not even close to being the same thing. This is what C.J. Date called the "First Great Blunder" which Adam and I have discussed before...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I actually agree with most of the things you say, possibly with the exception of the separating out toilet paper from sewage.
When ever I see a badly designed database supplied by a 3rd party organisation I get the warm glow of job security. If every database I saw was well built and well designed then what would people need me for?
-
Job security is nice, but in my experience, there are too many truly productive things that don't get done because so much effort goes into supporting junk systems.
I'm much more valuable to my company when we are actually swimming toward a goal as opposed to struggling to tread water. Ultimately it's my value to the company that determines how secure my job is.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply