Re: Offending My Tender Sensibilities -OR- OLTP on a Star Schema

On Tue, Mar 4, 2014 at 3:08 PM, Rob Sargent <robjsargent(at)gmail(dot)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