Re: pervasiveness of surrogate (also called synthetic) keys - Mailing list pgsql-general

On Thu, Apr 28, 2011 at 4:07 PM, Karsten Hilbert
<Karsten.Hilbert@gmx.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