[UPHPU] Database relationship problem

[Smith, Jeff]

-----Original Message-----
From: uphpu-bounces at uphpu.org [mailto:uphpu-bounces at uphpu.org] On Behalf
Of Scott Hill
Sent: Wednesday, August 16, 2006 4:53 PM
To: uphpu at uphpu.org
Subject: [UPHPU] Database relationship problem

After following the last database thread, it is obvious that there are
many who have lots of experience with database design.  So, I have a
question about a relationship problem in a genealogy database.  The
problem is given names.  It is very common for people to have one or two
given names.
However, there are many with more than two.  I have seen some genealogy
databases with 10 given name columns to accomodate this.  This is, of
course, a horrible violation of normalization rules.  You could create a
given name table with a link to the person table but this would create a
lot
of redundancy.   In other words, a lot of records  with the same common
name.  So, it seems to me that it's best to have a many to many
relationship and create a junction table.

Person table
ID
Surname
....

Given Name Table
ID
Name

Person Names Table
Person ID
Given ID

My question is what would the SQL look like to retrieve the person with
all of their names?  Or is there a better way to represent this
relationship?

--
Scott Hill

"May you solve interesting problems" - Author Unknown "A fanatic is one
who can't change his mind and won't change the subject." - Sir Winston
Churchill

_______________________________

I don't have a solution but I can add to the problem.  How would you get
the given name in the correct order.  You don't want confuse Billy Bob
James Jones confused with James Billy Bob Jones.