Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Design best practice of Status Lookup tables

Author  Topic 

madlo
Starting Member

41 Posts

Posted - 2013-05-05 : 10:38:03
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-05 : 20:56:22
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
   

- Advertisement -