Lookup Table Madness
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpeterson/l
/*****************
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
*****************/
-
In my opinion, you got a bit wordy which meant your proof for your ideas was less clear.
Other than that, I agree with what you said. I believe most of the problems I have come across relate to the original code using a technique that is possible and works (just), but is not necessarily the best way of doing it. Database design can suffer in the same way and you highlighted a good example.
-
CREATE TABLE Code (
CodeID int IDENTITY(1,1)
, CodeType int
, Description varchar(255))
ALTER TABLE Code ADD CONSTRAINT PK_Code PRIMARY KEY (CodeID, CodeType)
I would put CodeID as the PRIMARY KEY and separate index on the CodeType field.
The reasons being that I may want a drop down box of all entries for a particular code type.
As CodeId is unique I don't have to worry about what type I am referencing in a PK->FK relationship.
If I have a database with a large number of "Lookup" tables but these table only hold a handful of records each then I will consider the "MUCK" approach.
If those lookup table contain a vast number of records then I keep them as individual tables because a MUCK table will slow the system down.
It really depends on who is going to be using the database and for what.
-
Completely agree, it's what's best suited to the app. being created.
I'd use Access (spit!) for certain things, but not for a serious app/site/amount of data.
I wouldn't expect to see the MUCK approach in a serious project, certainly not in a commercial project (though I have, and it did cause hideous problems).
For personal usage or a small group who understand the implications of what's being done it's fine.
But the problem is, these things often creep into production, or worse still, get taken as an acceptable example by people who don't know any better.
I think the main argument for always doing it the right way is that if the DBA can't be bothered to do it right on EVERY occasion, some developers will see it as an excuse for them to go down the same route, even though they may not understand the implications of doing so.
All comes down to education - the old saying 'A little knowledge is a dangerous thing' is probably most apt here...
Jon -
This is a superb explanation of the subject. I completely agree, and I like the logic used that makes it more than just an opinion. In the rarest of occassions, like an import database that is more a staging area for information rather than a proper OLTP database, I can see the use for a MUCK, but otherwise no.
-
I agree also, there have been many arguments that i've had with programmers about the design of the database being more important and this article gets it spot on.
cheers
dbgeezer
-
A good article but your points got lost in the wordiness. A little brevity please.
-
I believe the 'wordiness' of the article has left me confused as to exactly what the articles author's opinion is about lookup tables. Is he making the case that no lookup tables should be used or that no lookup tables that combine multiple objects be used? At the beginning of the article he listed 3 independent lookup tables and then further down he made the case that these 3 tables could be combined into a single lookup table since they shared the same structure. Is the author stating that the use of lookup tables as defined at the beginning with the 3 individual tables that contain 1 specific type of lookup value only not be used, that a table that is a combination of these 3 should not be used or that neither of these approaches should be used?
If someone could help clarify this for me I would be most appreciative.
Thanks
Ed
Kindest Regards,
Just say No to Facebook! -
"Wordiness" is no the only thing that makes this article confusing. The authors zealotry <g> makes it even harder to wade thru...
Instead of giving a "holier than thou" talk, try pointing out some of the reasons why this particular design was used and is used today, and how it can be abused, and not abused...
Code tables (MUCK tables...ahhh give it a rest) can be (and have been) an elegant solution to specific design issues...
Hierarchical data has always and will always be around, the author of the piece painted a very narrow minded "take" on it...
Next time, add some facts to your diatribe...
Cheers
-
I totally agree with your message, but please slow down when you write. Maybe make an outline first, and have a few people edit it for you. You clearly have some good points to get across. I'd like to see you express them effectively.
One problem in particular is that you present your 'MUCK' table (thing) later in the article than you do the properly normalized tables (EmployeeStatusCode, CustomerStatusCode, etc). This is confusing; think about the flow of the article: Introduce problem (MUCK); show example of problem (MUCK tables); introduce solution (normalization); show examples of normalization (normalized tables)...
Again, good effort; execution just needs a bit of work.
--
Adam Machanic
whoisactive -
Code/MUCK tables (or more formally, entity-attribute-value tables) violate the first normal form and therefore have severe data integrity issues. Keeping an open mind, though, I would be very interested in an example of a situation in which you feel that they provided an elegant solution.
--
Adam Machanic
whoisactive -
The article was a little wordy but in general I understood what the author was getting across. For data integrity, each lookup table should be a separate entity with a foreign keys coming from each table that references (uses) the code. That said, I have also used (or been made to use) the MUCK approach, and yes, in production with some success. Of course, bad entries eventually made their way into the tables, but it was not very often.
-Vic
-
I plead guilty to being a zealot. I am absolutely a zealot when it comes to proper database design because I have to deal with the consequences of poor database design on a daily basis.
Now, as to your charge that I didn't add any facts, and that Code tables present an "elegant solution", I would ask you to provide some facts of your own. You and others have accused me of being overly wordy, and that might be the case to some extent, but I felt that it was necessary to fully illustrate the point. Part of my "wordiness" is due to the diffuculty in trying to deal coherently with that which is essentially incoherent. However your overly brief statement as to the "elegance" of code tables is hardly believable since you provide no evidence (and I don't believe that you ever could) unless your brevity is to be taken for proof...
Of course hierarchical data will always be around, so what? A properly designed relational database is perfectly capable of representing hierarchies, however, hierarchical databases (to include XML) have a very difficult time properly representing the more complex (and useful) "has a" logic for data that is not inherently hierarchical.
/*****************
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
*****************/
-
Thanks for the input. This article went through a number of iterations and was reviewed by at least 5 different people for readability. That's not to say that I couldn't have done a better job, but I'm not a professional writer.
A few notes: I presented the normalized table structure first because I was working on the assumption that that is the starting point and that the majority of the audience should be familiar with it. I wanted to show how well meaning, but generally ignorant, database designers actually ruin a well designed database by including this extra step. I further wanted to show how this extra step was not only not beneficial, it takes one back to all the problems that were resolved by normalization.
Not to blame others for any problems that might exist in the article, but many of the parenthetical points (and I know there are a number of them)
were actually in response to questions my various reviewers asked. "What about this?", "So, are you saying...?" etc.../*****************
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 would definitely like to see an example of a case where a MUCK table was a good solution. What is the value of this sort of design? Just fewer tables? I find that good database design not only enforces data integrity, but helps decode your data for future generations, especially if you're careful about naming conventions. I've had to reverse engineer more than one database in my career and let me tell you that that above anything else has made me a successful database designer. As such, I'm willing to acknowledge that certain deviations from convention are necessary for certain applications, I'm just curious as to the actual virtues of these so-called MUCK tables.
~Pam
The greatest obstacle to transforming the world is that we lack the clarity and imagination to conceive that it could be different. -- Roberto Unger
Viewing 15 posts - 1 through 15 (of 202 total)
You must be logged in to reply to this topic. Login to reply