Re: NextNumbers Tables

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