Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Lookup Table Madness Expand / Collapse
Author
Message
Posted Thursday, August 05, 2004 4:27 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408
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



*****************/
Post #130464
Posted Wednesday, September 08, 2004 2:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 18, 2004 3:44 AM
Points: 22, Visits: 1

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.

Post #135766
Posted Wednesday, September 08, 2004 2:48 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:49 PM
Points: 2,866, Visits: 1,708

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.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #135768
Posted Wednesday, September 08, 2004 3:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:34 AM
Points: 141, Visits: 61
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
Post #135774
Posted Wednesday, September 08, 2004 5:34 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:28 AM
Points: 788, Visits: 1,915
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.


Post #135793
Posted Wednesday, September 08, 2004 5:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 27, 2007 12:21 PM
Points: 342, Visits: 1
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
Post #135795
Posted Wednesday, September 08, 2004 6:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 06, 2006 10:20 AM
Points: 157, Visits: 1
A good article but your points got lost in the wordiness. A little brevity please.


Post #135806
Posted Wednesday, September 08, 2004 6:53 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, March 10, 2014 1:21 PM
Points: 885, Visits: 1,526

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,

A Democracy works great until the day you find yourself on the sheep side of a vote between 5 wolves and 4 sheep on what’s for dinner when neither have eaten in many days. A free Republic where the rights of the few and the individual are protected is the only one in which Freedom and Prosperity for all have a chance to blossom.
Post #135811
Posted Wednesday, September 08, 2004 7:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, March 21, 2014 7:45 AM
Points: 1,138, Visits: 698
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #135823
Posted Wednesday, September 08, 2004 7:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 21, 2013 9:49 AM
Points: 1, Visits: 2

"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




Post #135824
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse