dananrg@xxxxxxxxx wrote:
In the book "Practical Issues in Database Management", Fabian Pascal notes three reasons for choosing one PK over another - familiarity, stability, and simplicity. He notes further that those influenced by OO db design tend to use simple, surrogate keys for all PKs in all databases they design; that this is not *precluded* by relational theory, but that there's somehow something illicit about it. Today at least, and why I ask, I think it's a good rule of thumb to create surrogate keys for almost all tables. "Familiarity" seems like a spurious concern, and a poor tradeoff against both stability (guaranteeing you are uniquely identifying rows) and simplicity (with queries, and others intuiting your design). What am I missing? Why use a composite key *ever* aside from "familiarity?" Could someone give a real-world example where "familiarity" is a compelling reason to choose a composite PK, and trumps stability and simplicity? Stability seems to be the single-most important factor to consider. If the database can't uniquely identify a row, what's the point? Choosing a surrogate key guarantees stability.
Surrogate keys have the advantage of performance. A composite key composed of four fields of 50 characters each could create performance problems. But a unique serial avoids this problem. Of course, one of the drawbacks of surrogate keys is that, if you design your table so that those four fields together are unique, once you index the table on the serial key, there's nothing to guarantee your four fields will *stay* unique. Another reason for surrogate keys is that there may not be any *meaningful* combination of fields to make up a unique key. That is, although you may indeed have four unique fields for your table, together they really have no meaning. For example, I have a log table in one of my applications which adds several records per job. Yes, I could make the key jobno + sequence_number. But why not just let the primary key be a serial? Certainly simpler. Here's another reason for using surrogate keys (real world). You have a PHP application where you're passing GET parameters to the next PHP script in the chain. If you're going to query a table with one of these long composite keys, you've got to go through the operation of concatenating all these values together in order to pass it via the GET parameter. A real pain. It's far easier to retrieve the single serial key field and pass that. Overall, I'd say this is the problem with guys who write books full of theory, expecting students to buy their pronouncements. Yes, some of these guys have real world experience. But really, you should hang out with some people who actually do this for a living after you read the theory guys. The theory's keen and all that. But it's no substitute for getting your hands dirty in the real world. Things are often very different out there. Give me a mechanical engineer any day over an architect who's never been near a building. (I don't know much about Frank Lloyd Wright's education, but he built some beautiful buildings which didn't stand up well to the elements.) -- Paul M. Foster
