Re: candidate keys in abstract parent relations
Date: Thu, 19 Jan 2006 17:25:56 -0000
Message-ID: <dqoi24$505$1$8302bc10_at_news.demon.co.uk>
> 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.
No No - you see thats just the point!
If the value where 1KB and you had 100 items in the list box, I now have to
transmit 100KB to the client, on the internet thats a big deal; now multiply
that up to 10, 100, 1000 users and it becomes a really really big deal; even
saving a couple of KB's on a page really makes a difference in scalability.
Its the whole, better to send a character version of a number, say 10 bytes max than send a composite key or natural key that might be 100 bytes.
Using surrogate keys in the physical implementation is the whole package, you move away from using the natural key - you only use that for display as meta data so you only look it up when you want to show it.
>> 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.
Say you had a composite primary key, surname, firstname, dob for instance just for example sake.
The data would be Rogerson, Tony, 10/04/1970
The list box value would need to be Value="Rogerson, Tony, 10/04/1970"; but
what if that data contained comma's - what delimiter would you use to
seperate the composite keys? You'd probably need to use fixed length.
Whereas, plop a surrogate key on there say id=5 it would just be Value="5".
> 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.
My experience is the complete opposite, perhaps its just i've got used to cranking the most performance out of SQL Server in the early days because the hardware and disks weren't that powerful. I remember DB2 I didn't care because it was on a mainframe :)
Tony.
-- Tony Rogerson SQL Server MVP http://sqlserverfaq.com - free video tutorials "Roy Hann" <specially_at_processed.almost.meat> wrote in message news:wvOdncwyBLN_IVLeRVnyuQ_at_pipex.net...Received on Thu Jan 19 2006 - 18:25:56 CET
> "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
>
>