On Feb 10, 2012, at 10:49, Vincent Veyron <vv.li...@wanadoo.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); > > 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. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
