Re: NextNumbers Tables
Date: 18 Feb 2003 10:39:29 -0500
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
> > 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:
- They tend to get out of sync between development, test, and production. This in itself isn't an issue except:
- 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/powerteamReceived on Tue Feb 18 2003 - 16:39:29 CET