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.