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.
| 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 withhttp://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspxe.g.Status tableStatusCode (PK), Description,IsValid,IsEditable,IsViewableA, Approved,0,0,0D, Declined,0,0,0X, Deleted,1,0,1Why 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 toStatusID (PK), StatusCode,Descriptionor remove the CodeStatusID (PK), Description, etcShould 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 withhttp://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspxe.g.Status tableStatusCode (PK), Description,IsValid,IsEditable,IsViewableA, Approved,0,0,0D, Declined,0,0,0X, Deleted,1,0,1Why 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 toStatusID (PK), StatusCode,Descriptionor remove the CodeStatusID (PK), Description, etcShould 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. |
 |
|
|
|
|
|
|
|
Design best practice of Status Lookup tables