SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Lookup Table Design


Lookup Table Design

Author
Message
Leo Peysakhovich
Leo Peysakhovich
SSC Eights!
SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)

Group: General Forum Members
Points: 956 Visits: 367
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakh



amackie
amackie
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 195
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



James Yap
James Yap
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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?
Simon Sutcliffe
Simon Sutcliffe
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 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.





tskelley
tskelley
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 1186
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.




Michael R Schmidt
Michael R Schmidt
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 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
Calvin Lawson
Calvin Lawson
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2080 Visits: 102
Where could one get a list of these standard codes?

Signature is NULL
Peter Kryszak
Peter Kryszak
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1728 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!





pathallock
pathallock
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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





Michael R Schmidt
Michael R Schmidt
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search