Re: Simple question on data design...

Hello Ken,

> Consider tables with columns that contain data from
> a limited, pre-set list or domain, such as:

It depends. (Universal answer to general questions.)

Using the first letter is a kind of code for the spelled out value. There are many other ways to code. Your particular way limits you to 26 choices, although it could be extended. You could also use a numeric code.

 There are numerous implication to using codes for primary keys.

Let's say that, at some future time you wanted to make the system multilingual, and have the various values spelled out in English, French, German, etc. in separate columns. Using the coded values would probably make extension to multilingual implementation lots easier. The same concept applies to other attributes.

If you change your mind about the name of one of the attribute values, it's lots easier if that name is only stored in one place.

Depending on the population of your data, and on the length of the stored attribute names, you may achieve greater compression with codes.

Depending on your manner of using the data, you may be better off paying the performance penalty for reading bigger rows while avoiding a join, or you may be better off paying for the join when you need that attribute, but getting cheaper reads of the main table when you don't need the name of that attribute.

Like I said, it depends.

--
Regards,
    David Cressey
    www.dcressey.com