>I agree with the arguments relating to auditing & not reusing PKs, but if the version/revision are guaranteed to be unique, why not generate a PK based on the 2 values ? If I were designing a manual filing system, I would naturally try to file in order on something relevant to the document - making it easy to locate, in this case version/revision, just because it's a database system why do something different ? To my mind it seems to be 1 extra table to maintain, on top of any tables (procedures/whatever) to ensure unique version & revision numbering.

Paul,

Here's an answer. The purpose of a primary key is not to make an ordering of data for you and I, it is to allow the computer to connect records from two tables to each other. The computer has no idea what the meaning of the data is (the difference between data and information) and it doesn't care. Computers are very fast at processing integer numbers and they love them, especialy if we limit them to 1's and 0's. So for the most efficient method of allowing a computer to shlop through our records and connect them up correctly it is advisable to use integers.

Second reason, as soon as the primary key has meaning to a human being, they will want to change its value. "Oh damn, I put that in as revision 7 and it was really revision 8, I'll have to change that." Now you, the database programmer, must insure that the change in value of the primary key does not cause any orphaned related records anywhere. It may as simple as one other table, but it could be a whole slew of other tables that are involved.

With a surrogate PK (integer) you can let the user change the revision number and not give any thought to the related records because your primary key is not seen by the user, it has no meaning to the user, and it is strictly maintained by the computer, of the computer, and for the computer. Gee that last phrase sounds familiar :-).