Re: Strategy for Primary Key Generation When Populating Table

On Fri, Feb 10, 2012 at 7:49 AM, Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr> wrote:

> Le jeudi 09 février 2012 à 16:30 -0600, Merlin Moncure a écrit :
>
> > natural/surrogate is a performance/usability debate with various
> > tradeoffs. but using surrogate to 'create' uniqueness is a logical
> > design error; maybe a very forgivable one for various reasons, but the
> > point stands.
>
> Please consider the following case :
>
> I record insurance claims in the table below, where id_evenement,
> id_agent and date_origine define a unique event.
>
> However, records sometimes have to be canceled (set annule=true), and
> re-recorded the same way. They're normally canceled once, but
> occasionnally twice, or more (for various reasons).
>
> What would you use for a primary key?
>
> CREATE TABLE tbldossier (
> id_evenement text NOT NULL,
> id_agent integer NOT NULL,
> date_origine date NOT NULL,
> annule boolean DEFAULT false NOT NULL);
>
> First, a surrogate key will make joins more robust and so it should be
there. Also a partial unique index could be used.

We've had a similar issue with LedgerSMB and while our solution might not
apply to you it's worth mentioning.

We had an issue of storing sales tax rates which may change or expire at
some point, so something like:

CREATE TABLE tax (
id serial not null unique,
account_id int not null,
rate numeric not null,
valid_to date,
unique (valid_to, account_id)
);

Initially we created a partial unique index on account_id where valid_to is
null.

Later we changed valid_to to a timestamp and defaulted it to infinity.
This allowed us to declare account_id, valid_to as the primary key.

Best Wishes,
Chris Travers