Re: Normalization, Natural Keys, Surrogate Keys

Tobin Harris wrote:

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

Sometimes it makes sense ... sometimes it doesn't.

Daniel Morgan Received on Fri May 17 2002 - 01:18:22 CEST