Re: candidate keys in abstract parent relations
Date: Thu, 19 Jan 2006 16:21:06 -0000
Message-ID: <wvOdncwyBLN_IVLeRVnyuQ_at_pipex.net>
"Tony Rogerson" <tonyrogerson_at_sqlserverfaq.com> wrote in message
news:dqobtq$rha$1$8302bc10_at_news.demon.co.uk...
> It helps create a highly scalable database and provides an efficient
> interface into the applications using the database.
That is frequently asserted but I can't remember ever seeing any evidence to support that (admittedly appealing) intuition, and certainly nothing recent. I tend to feel this could be folk-wisdom rather than a law of nature.
> Consider a drop down listbox in say HTML, it has a VALUE which indicates
> which item in the drop down is selected, if we hadn't used a surrogate key
> (auto generated number) then we could well have a long value
What exactly is wrong with a long value? Who cares how long the value is? (Or more to the point, what does "long" really mean in this context?) If we're talking performance, the time it takes to transmit a 1kb value is an inconsequential fraction of the time it takes to lookup a surrogate key to get the value.
> or even worse a
> composite key in there - which begs the question, if your primary key is a
> composite key which often it is, how would you code that?
Don't understand the question.
> I get a real bee in my bonet over the surrogate key issue, I'm not saying
> you do this David because I don't think you do; but a lot of designers
> (especially celko) try to directly implement the logical model without
> regard for anything other than writing pure SQL against it. The database
> from a business stand point is there to store data.
Well I don't disagree, which is precisely why I resist putting anything but
business data into a database until the argument for doing so becomes
compelling. Folk wisdom, habit, rules-of-thumb, tradition, superstition,
and 10- or 20-year old design standards aren't very compelling.
Please don't imagine that I reject the use of surrogate keys, but I do find
there are great benefits if you can avoid them. They should be employed
only with the greatest reluctance.
Roy