Paul,

PMFJI, but you are trying to create a system that is meaningful to a person, not a computer. We had that discussion here, some were using a combination of fields which together were unique, but could be changed. When a portion of the primary key was changed, all related tables needed to be updated with the new foreign key. When we changed to surrogate keys, no additional processing was necessary.

Just my $0.02

Rex

>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.
>
>>>
>>>True, but I was assuming that as the original problem involved deletion & packing, then there would be no question of wanting historical information (e.g. for auditing)& any related tidying up of child records would be sorted prior to the deletion. In which case, there appears to be no significant difference in overwriting the original record & deleting then packing, except that overwriting doesn't require exclusive access to the table.
>>
>>The first thing the auditor will ask why was the data deleted? Then they'll tell you to go to a backup and get the data. IMO, you are much better off never reusing PKs.
>>
>>
>>>
>>>I must admit that I thought the idea of deletion/packing would have it's own problems, mainly for tracking old versions. I would have tended to index on version & revision - I guess this is a type of surrogate index anyway.
>>>
>>
>>Why go to all this trouble...just never reuse a key.
>>
>>>Paul