Choosing Primary & Foreign Keys & normalizing Person, Name, DOB & Wedding tables
-1
votes
1
answer
357
views
I am in the conceptual design of a database, building tables and views, selecting the PKs and FKs (primary and foreign keys) and normalizing.
Unfortunately, I am constrained to use Power Pivot on Excel to build the database, so where I would like to have a composite key I have to use an extra calculated column to generate a concatenation of columns as key.
Person (Name, DOB, Name-DOB [concatenated PK])
Name (Name [PK], Meaning, Origin)
DOB (DOB [PK], Astrological Sign)
I have a many-to-many recursive relationship that points from Person
to Person
, to map weddings. I need a linking table:
Wedding (Husband name, Husband DOB, Wife Name, Wife DOB)
Do I need a PK for the linking tables on the many-to-many relationships? If so, which? The only one I see (other than inventing a Serial Number
for each row) would be a concatenation of the 4 attributes in the table, because any concatenation of 2 or 3 attributes has a chance to be non-unique. (Say someone can have a 2nd wedding after their 1st spouse dies.) But isn't a concatenation of 4 attributes too long?
Do I need to normalize the linking tables on the many-to-many relationships? If so, how? 2NF (elimination of partial dependencies) and 3NF (elimination of transitive dependencies) are impossible since Husband DOB
depends only on Husband Name
(same for Wife
).
What FK do I use to link Wedding
to Person
? Should I use the Husband Name-Husband DOB
and Wife Name-Wife DOB
concatenations since Name-DOB
is the PK of Person
? If so, do I need to keep the individual attributes (Husband name
, Husband DOB
, Wife Name
, Wife DOB
) in Wedding
since they are not needed in the many-to-many relationship? What problems will it pose in updating information into the database via user forms if those attributes are not there?
Asked by user70277
Jul 9, 2015, 04:15 PM
Last activity: Oct 16, 2023, 09:06 AM
Last activity: Oct 16, 2023, 09:06 AM