Re: Normalization, Natural Keys, Surrogate Keys

> 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?

Regards,

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)
> ON UPDATE CASCADE
>
> 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 Thu May 16 2002 - 17:31:22 CDT