SQL table with a single column and incremental id

Question

Simple DB design question, that I honestly can't quite put my finger on.

On many occasions, I have a type table, which consists of an ID and the type's name. Ala. a language code table would have 2 columns

TABLE: language

language_id (1,2,3)                 
language_code (et, en-us, de)

Now I would always hand out the language_id as a foreign key to other tables,

BUT

Which is better?

To hand out language_id as foreign key and then make joins to get the language_code.

OR

leave out the language_id altogether so that we have

TABLE: language

language_code (et, en-us, de)

and then we will just hand out the codes as foreign keys and no joins will ever be needed, when we want to know, for example, what is the user's mother tongue.

Opinions?

Answer 1

The answer is, it depends.

Under most circumstances, a reference table with a foreign key is desirable. Here are a few reasons:

• You can include a foreign key reference that checks that the value is valid.
• You can readily add another value.
• You can include other information, such as the date added and a full name "English" rather than "en".

The performance hit is generally quite minor. You would have a primary key index on the language id and it would be very fast.

Your language code is only two characters. If you used a typical 4-byte key for the the reference, you would use up an additional two bytes in each record. There are situations where including the language code directly would save space and effort. However, for this to make a difference, you would be talking about an optimization on a very large database (say hundreds of millions of rows).

I would vote for a separate reference table and all the benefits that go with that. Under some extreme cases, one might consider an alternative structure for particular reasons.