Re: Strategy for Primary Key Generation When Populating Table

On Feb 10, 2012, at 14:56, Vincent Veyron <vv(dot)lists(at)wanadoo(dot)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 also create 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 will ultimately always end up with some form of artificial identifier. Since you do not appear to want to assign a new case number 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 use some kind of logging table to track changes in status. In that case each event only ever exists once (no versions) and you gain flexibility in handling different statuses (besides just open/annulled).

David J.