Re: Strategy for Primary Key Generation When Populating Table

On Feb 10, 2012, at 14:56, Vincent Veyron <vv.lists@wanadoo.fr> wrote:

> Le vendredi 10 février 2012 à 14:19 -0500, David Johnston a écrit :
>
>> One possibility is to add a "version" field (integer) and combine evenement and version to create the unique.  I'd
alsocreate a partial unique on evenement/annule to ensure you do not make more than one active version. 
>>
>
> Hi David,
>
>
> I don't find this solution very much different from using a surrogate
> key?
>
> Except that you then have to code it yourself, instead of using the
> built-in serial.
>
>

The version field has semantic meaning that a true sequential value does not.  If you are creating  new data then you
willultimately always end up with some form of artificial identifier. Since you do not appear to want to assign a new
casenumber when you annul and reopen an event you need some supplemental information to distinguish the two cases. 

I would argue you should remove annul from the table, optionally replacing it with a "current status" field, and then
usesome kind of logging table to track changes in status.  In that case each event only ever exists once (no versions)
andyou gain flexibility in handling different statuses (besides just open/annulled). 

David J.