Re: Separate PK in Jxn Tbl?
Date: 28 Jan 2008 23:43:10 GMT
Message-ID: <Xns9A33BE7E16B6f99a49ed1d0c49c5bbb2_at_64.209.0.89>
JOG <jog_at_cs.nott.ac.uk> wrote in
news:234b39d0-1c87-4565-81d8-69e7ffbee82c_at_b2g2000hsg.googlegroups.com
:
> On Jan 27, 8:39 pm, "David W. Fenton" > <XXXuse..._at_dfenton.com.invalid> wrote:
>> JOG <j..._at_cs.nott.ac.uk> wrote
>> innews:5afa9a80-f1c5-4ede-8095-1f4c0164417a_at_s12g2000prg.googlegrou
>> ps.co m:
>>
>> > No probs, although off the top of my head its gonna be a bit
>> > contrived. With an artificial key:
>>
>> > Marriages {id, husband, wife, date}
>> > Kids_from_Marriage {from_id, name, birth}
>>
>> > A query that asks "fetch me all the children whose mother is x"
>> > obviously requires an equijoin, matching Marriages.id and
>> > Kids.from_id. However with the original natural keys:
>>
>> > Marriages {id, husband, wife, date}
>> > Kids_from_Marriage {mother, father, name, birth}
>>
>> > The same query is a simple select. That certainly seems a lot
>> > less complicated to me ;)
>>
>> Assuming you've got some form of CASCADE UPDATE on your enforced
>> relationship, that will work.
>>
>> But it's repeating a huge amount of data,
> > I'd think adding two new unecessary columns full of data is rather > adding redundancy too.
But it's an efficient type of data (numeric, which is optimized in most database engines because it takes up less space and because numeric operations are optimized in most computing platforms), and it's STATIC data (i.e., once assigned, it doesn't change), whereas natural keys need to be kept updated over time (particular names).
>> and adding a bunch more
>> indexes to keep updated. Perhaps these are insignificant issues
>> to *you* and *your* apps, but my clients' apps (some using Jet,
>> some using SQL Server, some using MySQL) don't perform so well
>> when you add in all the overhead.
> > Again, I think you have missed the fact that James asked me for an > example where using an artificial key can complicate _queries_, > which I tried to provide. We weren't discussing anything else.
One can provide examples of anything if you ignore practicalities.
Many badly-designed spreadsheets that are used as databases provide exactly the same capability your query example provides, but I doubt you'd say those are preferable to a database that implementes surrogate keys. Yet, it's indistinguishable from your example.
>> And it all leaves aside the question of how you know that
>> husband/wife/date is always going to be unique. I think that on
>> any given day in the US, there are plenty of marriages in which
>> those three values will be identical. You could add place. But
>> then, in large cities, that might not be enough. So use Postal
>> Code in place of place, and that might do the trick, although in
>> large cities that might not do it, either.
>
> Well, yes what Marshall said, and I'll raise you an SSN.
SSN is not unique and it's not purely numeric, either. SSN + birthdate *is* unique, though, so a compound key of those two pieces of data for the two spouses would be a practical natural key.
But it's going to be very hard to get that data, and it also runs up against privacy issues.
-- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/Received on Tue Jan 29 2008 - 00:43:10 CET