Re: Offending My Tender Sensibilities -OR- OLTP on a Star Schema - Mailing list pgsql-general

From Merlin Moncure
Subject Re: Offending My Tender Sensibilities -OR- OLTP on a Star Schema
Date
Msg-id CAHyXU0xhv7SmSubX46UeprMKFfA0D+dcHLf+Sjkp7S6TT0W2YA@mail.gmail.com
Whole thread Raw
In response to Re: Offending My Tender Sensibilities -OR- OLTP on a Star Schema  (Rob Sargent <robjsargent@gmail.com>)
Responses Re: Offending My Tender Sensibilities -OR- OLTP on a Star Schema  (Rob Sargent <robjsargent@gmail.com>)
List pgsql-general
On Tue, Mar 4, 2014 at 3:08 PM, Rob Sargent <robjsargent@gmail.com> wrote:
> Do you make a distinction between a key and an index?  I'm not picking up on
> design-by-natural-key and what that entails. Especially the notion that the
> natural key of a given item might be mutable. What stops it from colliding
> with the next item? (I have not had the pleasure of working in a domain
> where natural keys are obvious if they existed at all. "What's in a name",
> after all. )

If your keys are mutable then you definitely have to take that into
consideration for key style choice...but not for your stated concern.
 Even though you can cascade (via RI) updated keys to various tables
performance can certainly suffer vs a surrogate.  This is the main
reason not to use natural keys: slow, perhaps even pathologically slow
update performance on the key.

However, collisions are a reason *to* use natural keys.  If you can'd
handle them with your proposed key then either:
a) you've misidentified the key
or
b) you'er allowing duplicate unique entries in the system and when you
should not be

Even when using surrogates, it's still a good practice to identify
what makes a record unique wherever possible and place unique
constraints on those fields.

merlin


pgsql-general by date:

Previous
From: Steve Crawford
Date:
Subject: Re: Mysterious DB reset
Next
From: Rob Sargent
Date:
Subject: Re: Offending My Tender Sensibilities -OR- OLTP on a Star Schema