• I'm in agreement with G2. Surrogate keys (which my company calls RIDs) are the only way to go for primary keys in entity tables--tables which contain entities. Aside from eliminating the need for cascade updates on primary keys (shudder) RIDs give far higher performance in joins and even RBAR situations. Our RIDs are 4 byte integers but even 8 byte integers would be faster links than long varchar keys.

    Tables can also have natural keys--which I've always heard referred to as Alternate Keys. AKs are usually a name of some sort. AKs for us are almost always to enforce a unique constraint, sorting, and the like. Nearly every entity will have both a PK and an AK.

    As for intersection tables the only reason I could see to give an intersection table its own surrogate key would be for auditing purposes in a combined audit table. If you don't care about auditing the intersection then the intersection table can (and should) consist soley of two surrogate keys.

    I'm somewhat confused by the author's thinking that AKs wouldn't be used as a matter of course in conjunction with surrogate keys. What am I missing?