Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: NextNumbers Tables

Re: NextNumbers Tables

From: Daryl Richter <drichter_at_pwrteam.com>
Date: 18 Feb 2003 10:39:29 -0500
Message-ID: <m2d6lpfvim.fsf@DJR-iBook.what-soft.com>


Alan Gutierrez <ajglist_at_izzy.net> writes:

> Pablo Sanchez wrote:
> > saul_margolis_at_hotmail.com (Saul Margolis) wrote in
> > news:c20aa081.0302140208.4f10eee6_at_posting.google.com:
> >
> >>This is exactly the way I try to do things, having a surrogate key
> >>and natural key with a unique index.
> >>Ever tried tracing a transaction manually (as one is sometimes
> >>forced to do) when you only have a natural key consisting of 10
> >>fields?
>
> > Exactly! This is why when people jump up and down about how surrogate
> > keys shouldn't be used, I shake my head and realize that these folks
> > have never worked on a large production environment.
>
> Would someone with experience using natural keys in a large production
> environment please chime in. I'd like to hear how using natural keys as
> primary keys in a large production environment makes things faster,
> easier, and better.

I design and manage databases in a medium-large (>50 G total data, at least a thousand tables over 5+ databases) 24x7 environment and I would say that the answer is "it depends." :)

You need to balance the convenience and simplicity of the surrogate keys against their brittleness. The two main problems I find with them are:

  1. They tend to get out of sync between development, test, and production. This in itself isn't an issue except:
  2. They tend to "leak" out into the application programs and before you know it you have hard-coded [fooIds] everywhere. Because of this I now disallow direct table access and only allow select through views which do not contain the surrogate key. In other words, the surrogate key is a physical implementation detail/issue and not part of the logical model exposed to programmers.

I also always define the true candidate key as an alternate key. I fully agree that a table with its only key as a surrogate key is a lurking problem.

>
> Or just a pointer to a strong argument.
>
> Alan Gutierrez - ajglist_at_izzy.net
>

-- 

Daryl Richter
Technical Manager, EAI Group
Exelon Power Team
http://www.exeloncorp.com/powerteam
Received on Tue Feb 18 2003 - 09:39:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US