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:
Name-DOB (concatenated PK)
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:
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 !