SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Design best practice of Status Lookup tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

madlo
Starting Member

29 Posts

Posted - 05/05/2013 :  10:38:03  Show Profile  Reply with Quote
I read an article on sqlteam here that says data belongs in your tables not in your code which I agree with
http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

e.g.Status table
StatusCode (PK), Description,IsValid,IsEditable,IsViewable
A, Approved,0,0,0
D, Declined,0,0,0
X, Deleted,1,0,1

Why not go further and remove the dependency on the 1 length char as PK and add a tinyint Primarykey that is used in the linking table as FK.

So change the above to
StatusID (PK), StatusCode,Description
or remove the Code
StatusID (PK), Description, etc

Should the primary key of a lookup table be the char value or just the tinyint type. My logic says that use the smallest integer type as it uses less space in the lookup table and the fk column in the table referencing it. However I still mostly see 1 char primary keys in most databases even large vendor databases as well as the article above.

I would like to have the design choice explained to me for which design cases to choose a character and for which to choose the smallest int(tintyint) for the Primary Key of a lookup table.


James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 05/05/2013 :  20:56:22  Show Profile  Reply with Quote
quote:
Originally posted by madlo

I read an article on sqlteam here that says data belongs in your tables not in your code which I agree with
http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

e.g.Status table
StatusCode (PK), Description,IsValid,IsEditable,IsViewable
A, Approved,0,0,0
D, Declined,0,0,0
X, Deleted,1,0,1

Why not go further and remove the dependency on the 1 length char as PK and add a tinyint Primarykey that is used in the linking table as FK.

So change the above to
StatusID (PK), StatusCode,Description
or remove the Code
StatusID (PK), Description, etc

Should the primary key of a lookup table be the char value or just the tinyint type. My logic says that use the smallest integer type as it uses less space in the lookup table and the fk column in the table referencing it. However I still mostly see 1 char primary keys in most databases even large vendor databases as well as the article above.

I would like to have the design choice explained to me for which design cases to choose a character and for which to choose the smallest int(tintyint) for the Primary Key of a lookup table.




While in theory that sounds nice, and may get the blessing of theoreticians who may like everything normalized to the N'th degree, there is a cost to doing it - namely, the cost for querying, inserting and updating data. If there are only a handful of status codes, probably known in advance, and probably generally known by their single letter acronyms, then it is just better to keep that one letter acronym as the primary key. Similarly, if there are only a few states for those status codes and they are known in advance, keeping them as columns such as IsValid, IsEditable and IsViewable is better.

From a storage perspective, a fixed CHAR(1) takes up a byte, so there isn't any difference between that and using a tinyint.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000