Oh, and he wasn't trading a check constraint for a
relation, he removed the lookup table and enforced the
permissible column values with a check constraint.
That's ok if you only have a few values (as in his
example, M/F/U). Lookup tables are good for more values
and when the user wants to change them all the time.
He does push a composite primary key, which has its pros
and cons. I hate them since they can usually be updated,
which can cascade changes throughout your database if
things go wrong. After I killed a database that way, I
don't do it anymore. A database also takes much more of
a performance hit if joining on tables with composite
keys (depending on size).
An alternative I prefer is to have an identity primary
key with a unique index on the four columns in his
example. This gives me the performance I need, the ease
of use I want, and the business rule enforcement
necessary. A little more work, but not much.
Darell,
I too have gone the identity + unique index route, and
haven't looked back. I guess that when it comes to
lookup tables, my first choice IS to have one, seeing as
my users over time ( rather short periods of time
actually ) add more and more values. In cases where I
have M/F type things that really aren't gonna change
much, then I use check constrainsts there.
Anyone working in the medical field can attest that even
simple things like gender are not so obvious. I've seen
standards that define 5 or 6 gender codes, and there's
nothing like different medical facilities insisting on
their own set of gender codes before they will buy your
software. I'm just using this as a silly example of how
even the simplest of cases is not so simple afterall, so
I very much appreciate lookup tables based on experience
in the real world over the technical wisdom of the
academic.