Re: Normalization, Natural Keys, Surrogate Keys

From: Tobin Harris <>
Date: Thu, 16 May 2002 23:31:22 +0100
Message-ID: <ac1brd$m1sdm$>

> Amen. It is a violation of First Normal Form (1NF); the column with
> the value is not one kind of attribute. In practice it is a nightmare
> becuae every code winds up being stored as VARCHAR(n) and converted to
> temporal, fixed length strings, numerics, etc. over and over for the
> conversions. A good rule of thumb in the schema design stage is:
> 1) mycode <datatype> NOT NULL DEFAULT <value> CHECK (mycode IN
> (<list>)
> for relatively short lists with constant values over time (sex, race,
> states, etc.)

Without sounding too dim, what you just said has confused me. It seems like you're denormalising the schema here?

Are you saying that you shouldn't store a list of codes in a separate table (providing they're not supposed to change)? I've been doing this for a while. I normally find that codes are 'meaningful' entites. The least I do is describe their meaning in a 'description' column. Also, I find that sooner or later, you need to associate data with these types. For example, if you have a 'states' list, the client may come back one day and say 'I need to flag a few states as being free delivery, can we do that?'. In which case I'd put a IsFreeDelivery column in the State entity.

I get the feeling I'm not doing something right here, so I would be greatful for futher explanation?


Tobin Harris

p.s - Joe - I think this is the second time this week you'll have corrected my viewpoint on things. I hope this doesn't become too much of a common occurance!

> 2) mycode <datatype> NOT NULL DEFAULT <value>
> REFERENCES CodeTable(mycode)
> for relatively long lists with values that are expected to change over
> time (stock ticker codes)
> >> Can somebody please comment on this? Am I totally wrong? <<
> You are not totally wrong. Of course it is hard to do diagnosis at a
> distance ...
Received on Fri May 17 2002 - 00:31:22 CEST

Original text of this message