>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.
>
>Sorry if I seem to be labouring the point, but as I previously stated, I'm trying to learn from people who have far better experience of building database systems than myself.
>
The lookup values are meaningful to the user...and can be changed by the user. These may be good for a Candidiate Key...but not the Primary Key. There is nothing stopping you from creating a lookup value for the user...in fact you will need it. Make the PK totally meaningless, ie a surrogate. One additional table to support is not a problem. The code to create the next key is not that complicated and only needs to be written once and then placed as a stored procedure. There is code available on my web site.
Craig Berntson
MCSD, Microsoft .Net MVP, Grape City Community Influencer