@Kazan said:Surrogate Keys vs Natural Keys is one of the biggest and oldest flamewars in the DB world. You and I are obviously on different sides of this one. I'll give you a few simple reasons why surrogate keys are better.
I don't necessarily disagree, except with your implication that only one or the other should ever be used. In most cases, surrogate keys are both a must and a convenience. But what I was referring to wasn't a natural key, it was a just a composite key.
@Kazan said:Do you think it is safe to have SSNs as PRIMARY KEY? If you say "yes", then you'd be WRONG. A) uniqueness is not a gaurantee SSNs can make B) they can changeDo you think it is safe to have PRIMARY KEY(First Name, Middle Name, Last Name)? if you say "yes", then you'd be WRONG A) uniqueness cannot be gauranteed B) they change change.
No, and no, these are good examples of bad primary keys.
In this table's case, the table is one that associates a cost with a product, keyed by gender and age. The "natural" key would be (Foreign key to other table's surrogate key, age, gender). It's not subject to change, and it's the only thing by which the table would ever be queried (if the database was used properly - in this case, the table is only ever queried without a select clause). And it needs to be a unique combination. And that table is static data so if a change is drastic enough we don't really have a problem with dropping it and recreating it on the next version. And there's only one column other than the key columns.
All these factors together make me feel this is a good case for using a composite key.
That all said, my problem isn't that they used one rather than the other. It's that they don't know why they did so. I don't care what they did; I just want them to think before they act.