Sample Header Ad - 728x90

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