Problems in Choosing Foreign Keys :S

Question

I am designing my very first relational database. I am now in the conceptual design, building tables and views, selecting the primary and foreign keys and normalizing everything. But now, I am stock with a particular table; I have a hard time selecting the appropriate foreign keys and I need some help. Here is a fictive, simple case to represent the relationships where I have a problem. Let's say I have those 3 tables:

Person Table

Name

DOB

Name-DOB (concatenated PK)

Name Table

Name (PK)

Meaning

Origin

DOB Table

DOB (PK)

Astrological Sign

So far it's okay, but the next table is where I get confused. Let's say I have a many-to-many recursive relationship that points from my "Person Table" to my "Person Table" again, to map the weddings. For this many-too-many relationship, I need a linking table. This table (so far in my design) goes as follow:

Wedding Table

Husband name

Husband DOB

Wife Name

Wife DOB

Now for the "Wedding Table" I have some questions:

Question 1: Do I need a Primary Key for the linking tables on the many-to-many relationships ? If so, what PK may I use ? The only one I see (other than inventing a random "serial number" for each line of data) would be a concatenated of the 4 attributes in the table, because any concatenate of 2 or 3 attributes has chances to be "not-unique" (let's say someone can have a 2nd wedding when the 1st husband/wife dies, for example. I'm sorry, that's the best example of fictive tables I found to illustrate my real life problem). But isn't it too long, a concatenate of 4 attributes ?

Question 2: Do I need to normalize the linking tables on the many-to-many relationships ? If so, how may I do that ? Because it appears to me that a 2nd (elimination of partial dependency) and 3rd (elimination of the transitive dependency) is impossible since the Husband DOB depends only on the Husband Name (same for the wife)...

Question 3: What Foreign Key do I use to link the "Wedding Table" to the "Person Table" ? Should I use the "Husband Name-Husband DOB" and "Wife Name-Wife DOB" concatenates since the "Name-DOB" is the PK of the "Person Table" ? If so, do I need to keep the individual attributes (that is Husband Name, Husband DOB, Wife Name and Wife DOB) in the Wedding Table since they are not needed in the many-to-many relationship ? Will is pose any problem in the updating and feeding of information in the database via user forms if those individual attributes are not there ?

Thanks to anyone who can help me with my questions !

Answer 1

I will be using MS SQL terms below but you can translate them into the DBMS that you will be using. I also made some assumptions that seemed logical, so you can drop those if they do not fit your model. I would setup the tables in the following manner:

Person Table
PersonID int PK
FName Varchar(50)
LName varchar(50)
DoB Date FK

Name Table
Name varchar(50) PK
Meaning varchar(MAX)
Origin int FK

DOB Table
DoB date PK
AstrologicalSign int FK

Wedding Table
WeddingID int PK
HusbandPersonID int FK
WifePersonID int FK

Origin Table
OriginID int PK
OriginDescription varchar(50)

Astrological Sign Table
AstrologicalSignID int PK
AstrologicalSignDescription varchar(50)

I am a visual person and it helps me to see things laid out graphically, so I am attaching a diagram as well.

The personID, astrologicalsignID, weddingID and originID are all setup as identity fields that auto increment. I can provide a SQL backup of the database if that would help you.