How should I build "Type" tables? 2

[dragonwell]

For instance a "State/Province" table, where all we really care about is a 2-character code, and a long name.

The options I see are:
1. use the 2-char code for the PK and FKs in other tables
2. Put in an integer column for the PK and use that for FKs in other tables, and put a unique index on the 2-char code and use the 2-char code in the app's business-logic code.

Really with the State/Province example it seems obvious to just use the natural key, but with other more app-spcific types, I'm not sure which way to go. It seems that if you use the 2-character code for a pk, then you run out if you get over 104 types.

For instance, my app has a JobType table, with maybe 15 row at most. I know I want a unique, 2-character code for each that I will use in the business-logic, as well as a varchar(50) for the actual name of the job-type. The question is, do I simply make the 2-cahr code the PK, or add an int column for the PK - i.e. - " a key unrelated to the data"

?

Hope that makes sense.

-d

 

[stefanwagner]

Although 26*26 > 104 in my opinion, I would suggest:
PK should be an int with no meaning.
You may use an acronym (2 char) and the whole province (50 c) too. If you create programs on this logic, you may find out, to use it in other circumstances, where you need 3 or 4 char-acronyms.
I would suggest a unique index for the acronyms, since an acronym should be unique in most circumstances (unique in it's scope of course.)
If in another context 2-char-acronyms don't fit, you may make them larger, but still use uniqueness.

 

[stevexff]

Agree with Stefan here, as if your customer base expands to an country where 2-character state codes don't apply, you have a problem. You can hide the gory details of the 'real' primary key behind the interface. And using an int gives you two billion possible locations instead of just 104, which *should* be enough for most implementations...

 

[stevexff]

...and of course, it's not 104 but 676

 

[dragonwell]

Thanks for the tips!

>>it's not 104 but 676

Doh! I must have been up WAY too late...