Re: Separate PK in Jxn Tbl?

JOG <jog_at_cs.nott.ac.uk> wrote in
news:5afa9a80-f1c5-4ede-8095-1f4c0164417a_at_s12g2000prg.googlegroups.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, 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.

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.

Given that I can foresee a reasonable possibility of a collision on this candidate key as currently defined, I'd think long and hard on whether to use it or not.

And it's one of the main problems any time you're storing data about people in a data table -- you often lack pieces of the information, and you run a high risk of collisions between people with the same names.

And that fact of the real-world entities being modelled makes finding a natural key that will work as a PK a very hard task.

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/