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 123»»»

Lookup Table Design Expand / Collapse
Author
Message
Posted Monday, June 28, 2004 10:34 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakh


Post #123495
Posted Tuesday, July 27, 2004 2:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 21, 2014 5:45 AM
Points: 12, Visits: 150
Joe Celko gives the argument against design 2, the "One True Lookup Table" (a.k.a "monster of all lookups") at http://www.dbazine.com/celko22.shtml


Post #128370
Posted Tuesday, July 27, 2004 3:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 26, 2007 8:48 AM
Points: 1, Visits: 1
I don't really have much experience with DB design but the sql query in design 3 with the inner joins seems to put more work on the DB server, doesn't it?
Post #128375
Posted Tuesday, July 27, 2004 5:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 8, 2009 2:01 AM
Points: 33, Visits: 6

It's not completely necessary to use a textual method for identifying the group of related codes.

Similarily, having a status based on a textual value is also not ideal.

eg.

Use one table to contain a list of all the possible Code Groups :-

CREATE TABLE [dbo].[tbl_Code_Group] (
 [Code_Group_Id] [udt_Code_Group_Id] IDENTITY (0, 1) ,
 [Code_Group_Descr] [udt_Descr] NOT NULL ,
 [Code_Group_Type] [udt_Code_Id] NOT NULL ,
 [Code_Group_Status] [udt_Code_Id] NOT NULL ,
 [Code_Group_Purpose] [udt_Text] NULL ,
 [Create_Date] [udt_Date_Time] NOT NULL ,
 [Create_User] [udt_User_Id] NOT NULL ,
 [Update_Date] [udt_Date_Time] NOT NULL ,
 [Update_User] [udt_User_Id] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Another is used to contain all the possible codes :-

CREATE TABLE [dbo].[tbl_Code] (
 [Code_Group_Id] [udt_Code_Group_Id] NOT NULL ,
 [Code_Id] [udt_Code_Id] NOT NULL ,
 [Language_Id] [udt_Language_Id] NOT NULL ,
 [Code_Descr] [udt_Descr] NOT NULL ,
 [Code_Descr_Short] [udt_Descr_Short] NULL ,
 [Code_Mnemonic] [udt_Code_Mnemonic] NULL ,
 [Code_Text] [udt_Text] NULL ,
 [Code_Type] [udt_Code_Id] NOT NULL ,
 [Code_Status] [udt_Code_Id] NOT NULL ,
 [Code_External_Ref] [udt_Code_External_Ref] NULL ,
 [Code_Display_Status] [udt_Code_Id] NOT NULL ,
 [Create_Date] [udt_Date_Time] NOT NULL ,
 [Create_User] [udt_User_Id] NOT NULL ,
 [Update_Date] [udt_Date_Time] NOT NULL ,
 [Update_User] [udt_User_Id] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

NB: All columns with "_Id" are of type Integer.

Provided you know the numeric Code_Group_Id you can always retrieve the appropriate Codes for the Code Group. Having additional fields for external refernces / Mnemonics where they are needed implies you can lump it all into this one table.

If you then write an object class to deal with the Codes it is very simple to encorporate all the appropriate functionality of the tables into your application using fairly straightforward programming constructs.

The Use of a Language_Id in the second table facilitates support for multiple language applications.

Note, The Code_Status is itself another Code_Group_Id as is the Code_Type and Code_Display_Status.

Basically, I never use any non-numeric keys for any form of codes. They are always translated through the Code table. Keeps life really simple.

Lots of ways to skin the cat I suppose.




Post #128398
Posted Tuesday, July 27, 2004 7:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:20 PM
Points: 39, Visits: 898
I definitely agree with having the Code_ID field an Integer (and/or AutoNumber), especially when it becomes a foreign key.  It gets kind of ugly dealing with user-entered values as the foreign key (and gets a little uglier with Oracle).  Not to mention, it gives you much more flexibility with your design.  We typically include a CODE_ID column with the explicit purpose of allowing the user to enter a code if they prefer or if we are synching/importing from another database. 
 
As for the dilemma Joe Celko references with the excessive paging, this can always be prevented/aided with a clustered index on the Group_ID field.
 
And lastly, you will probably want some type of Order column and maybe even a Status column.  At some point, the users will want a particular order or grouping of the lookup values.  And eventually, they will want to "mothball" the values so they do not appear in their dropdown lists.  We combine both into one column by allowing them to order the values 1..whatever, and even duplicate numbers they want grouped together.  To prevent the value from showing in the dropdowns, we have them set the value to 0 or a neg. number.  And yes, I know, you usually do not want a column to serve as a "two-headed beast", but, so far, this has been well received by the users.  The neg. values only hide the values from new data entry values, but the reports do not limit on this column.
 



Post #128447
Posted Tuesday, July 27, 2004 11:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 30, 2006 3:20 PM
Points: 34, Visits: 1

Ok, I am sorry - not really - but this is design madness. 

Seperate tables for each lookup table.  Surrogate int keys when appropriate (almost always).  This keeps referential integrity possible - hello thats the point of a relational database.

ISO codes - give them a column with a unique constraint.

Never, never, never design anything that uses triggers for referential integrity unless it is as a last resort.

I want my lookup tables to be easy to use

Select F.Comments, S.StatusCode

From Form F

Join Status S

On F.StatusID = S.StatusID

Where F.FormID = 34567

There, I have comment and the text version of the status information, something the user can read.

Also, if I want to cache Status Code information somewhere the table is small and light so it is easy to cache as say XML




Michael R. Schmidt
Developer
Post #128529
Posted Tuesday, July 27, 2004 12:31 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102
Where could one get a list of these standard codes?

Signature is NULL
Post #128560
Posted Tuesday, July 27, 2004 1:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 7:51 PM
Points: 1,244, Visits: 3

I think that the article is on target.  I am no newbie and I have grown to love appreciate the combined code table (scenario 2).  I would say that this solution is most useful for codes that are defined within an application and hidden for most external use.

In my experience it is not too confusing to use a self-reference for code type values within the table, but I have yet to see referential integrity enforced in the database, as it is just too blind and disallows a great deal of flexibility and value.  That's another topic I suppose.

Celko's points are valid when the codes are externally defined and strongly typed/formatted.  I would never put country codes in the same table with diagnosis codes.   In some of these cases the number of codes is very large and, in any case, deserve separate maintenance.

Also, I've found that a simple function call is SOOOOO much better than a join to do a code lookup!




Post #128567
Posted Tuesday, July 27, 2004 7:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 20, 2007 1:36 PM
Points: 3, Visits: 1

Usually when an abritrary ID is used in place of actual codes it is necessary to provide alternative keys to assure uniqueness of the values. The patterns here do not show any alternative keys so I can only assume there are none. Most designers forget that is you have (code, description) The code is the primary and the description must be an alternate key to prevent duplicate descriptions. Now add in a new value such as (ID, code, Description) Now the primary key is probably ID + Code - but remember you must also have ID + Description to prevent the duplication. With each additional component to the pattern the issue of maintinaing unique codes and descriptions become more difficult. A real world example. The city was concerned that not enough tax revenue was created by a certain property type. Upon a one-minute investigation it was found that 5 codes had the same description. So, if you did not ask for all 5 codes you would not get the right answers. SOlution - assign one code + description then update all the bad records to that one code-description and what do you know - revenue. Without assurance that this holds true in this design I hold a reserved opinion. (No duplicates of any columns in lookup tables)

Pat




Post #128640
Posted Wednesday, July 28, 2004 9:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 30, 2006 3:20 PM
Points: 34, Visits: 1
I would prefer ID, Code, Description where ID is identity int and Code is unique.  Not sure I care if description is unique although I can see that as a possible advantage.  One issue with all data is to apply the 18 inch rule.  Whoever is 18 inches in front of the monitor has to be responsible for the data that is input.  Lookup tables should be as static as possible and therefore any additions to a lookup table need to be made very carefully.


Michael R. Schmidt
Developer
Post #128829
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse