Skip site navigation (1) Skip section navigation (2)

Re: pervasiveness of surrogate (also called synthetic) keys

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: Karsten(dot)Hilbert(at)gmx(dot)net
Subject: Re: pervasiveness of surrogate (also called synthetic) keys
Date: 2011-04-29 15:14:07
Message-ID: BANLkTikqYePddvfLyeKC2i3PNa4qXM2WFA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Thu, Apr 28, 2011 at 4:07 PM, Karsten Hilbert
<Karsten(dot)Hilbert(at)gmx(dot)net> wrote:
> On Thu, Apr 28, 2011 at 03:39:19PM -0500, Merlin Moncure wrote:
>
>> They are fairly pervasive, and increasingly so, which I find to be
>> really unfortunate.  Personally I think rote use of surrogate keys is
>> terrible and leads to bad table designs, especially if you don't
>> identify the true natural key with, say, a unique constraint.
>
> I was recently asked on this list why GNUmed uses all those
> surrogate keys.
>
> I should have added to my answer that we DO make *extensive*
> use of all sorts of built-in constraints and custom triggers
> to enforce "natural" keys. I must agree with a recent poster
> that what appears to identify as a natural key often really
> isn't or else becomes not so later on. It's vastly easier to
> then deal with that by re-defining constraints without
> having to touch primary keys.
>
>> This
>> pushes duplicate enforcement out of the database and into application
>> code, or (even worse) the user.  What everyone misses in the database
>> is that natural keys force good database design...if you can't
>> discover one, you probably have a design problem.
>
> Sure but that doesn't mean you need to actually *use*
> natural keys as primary keys - enforce them with all sorts
> of constraints, sure - but don't assume you properly figured
> out the perfect schema the first time around.
>
> I've so far found it *good* to have duplicate enforcement:
>
> - the database enforces what we agree on the final data
>  *should* look like
>
> - the UI tries to lure the user into entering "valid" data
>
> Now, those ("duplicative") database constraints have saved
> our butt more than once preventing faulty patient data to be
> inserted into medical records.
>
>
>> A lot of bad arguments made against natural keys are made, for example:
>> *) natural keys can change (so what? unless you are worried about cascades)
>
> I find it is not so much that they can change:
>
>        Sure, it doesn't matter whether a certain code reads
>        "C03EB21" or "C03EB22".
>
> but rather that they tend to go non-unique whenever the
> whims of meatspace decide it's now more convenient to allow
> dupes:
>
>        Suddenly there must be two records with code "C03EB21".
>
>> *) SSN are reused! (SSN is obviously not a complete key if you want to
>> identify a person)
>> *) most tables don't have unique natural keys (let's see em)
>
> Now, those two arguments are bogus, I agree.

pretty much agree on all points.  I don't really think primary keys
tend to change very much in terms of schema but when they do it can be
a real headache.

I took a quick look at the gnumed schema and found it to be generally
very thorough and excellent.  If you're going to use surrogate keys,
that's they way to do it.  That's a neat trick btw to use inheritance
for the auditing feature...how is it working out for you?  Any general
comments on postgresql with regards to your product?

merlin

In response to

Responses

pgsql-general by date

Next:From: RaghavendraDate: 2011-04-29 15:25:29
Subject: Re: Partitioning an existing table
Previous:From: Vick KheraDate: 2011-04-29 14:47:01
Subject: Re: Partitioning an existing table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group