Re: Surrogate VS natural keys

On 6/20/07, Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:
>
> --- "Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
> > The value of a surrogate key is easy retrieval and really has nothing to
> > do with normalization or proper modeling.
> >
> > I often add a surrogate key, even when one is not required just so I
> > don't have to worry about have a 4 element where clause.
>
>
> I've often wondered about this. Since PostgreSQL allows FOREIGN KEYS to be referenced from UNIQUE
> (non-primary) natural keys, couldn't the schema be designed so that every table has a surrogate
> PRIMARY KEY and yet still maintain the relationships using UNIQUE natural keys.
>
> Would a design like this be practical?

yeah, although I prefer to throw the primary key on the natural.
Either way, the natural key is identified...my major issue with the
surrogate design style is that the natural key is often not identified
which inevitably leads to a mess.

I also find databases with natural keys to be much easier to follow
and feel much 'cleaner' to me. People who've never seen a large
database without surrogates will be amazed at how much more expressive
the tables are. Surrogates have certain advantages but I classify
them as an optimization, meaning they should be introduced at the last
possible moment in the design.

merlin