0

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 !

  • 3
    I stopped at the first table, sorry. Why are you choosing name + DOB as a concatenated key? Do you think it's impossible to have two John Smiths born on the same day? Use a surrogate key and be done with it - then your alternate key can include additional information to help distinguish two John Smiths. – Aaron Bertrand Jul 9 '15 at 16:20
  • And with "concatenated", do you mean you plan to have a 3rd column with the same data as the other 2? This is redundant and problematic. If you do need a composite PK, you can have a PK from 2 columns, no need to duplicate the data. – ypercubeᵀᴹ Jul 9 '15 at 16:24
  • Your wedding table should just be (person + spouse) - with a UNIQUE key on spouse - PK on person - unless we have Mormons or something like that. Your DOB table - with DOB as a PK - really? You can't have two people with the same birthday? – Vérace Jul 9 '15 at 16:59
  • @Vérace the DOB table looks like it will be used for astrological queries. Born on YYYY-07-27? Leo. Born in YYYY-01-31? Aquarius. – ypercubeᵀᴹ Jul 9 '15 at 17:51
  • Yeah - OK, but surely people born on the same day have the same sign? – Vérace Jul 9 '15 at 17:53
0

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.

DatabaseDiagram

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.

  • Thank you very much for your answer ! I have a few questions if you don't mind. 1- what would be the advantage of using a PersonID or WeddingID (which are randomly allocated numbers I presume) as the PK as opposed to using a composite PK made of (FName, LName, DOB) in the hypothetical scenario where it is impossible to have 2 persons with the same name born on the same day (in my real project, a duplicate is impossible). 2- Why do you use the Origin Table and Astrological Sign Table ? aren't they somehow one-to-one relationships ? I was told to keep the number of tables to minimum... no ? – user70277 Jul 10 '15 at 13:37
  • 1. I was thinking of a real world scenario where there is the possibility of someone with the same name and DoB existing. Creating a key and index off of a small value is also more advantageous in larger databases. The size of the indexes that are created are directly related to the amount of data that is stored as a key. If you use all the fields as a key you are essentially doubling the size of your table because the index will be the same size. When queries are run against the tables the seeks or scans will also be faster because there is less data to compare. – user16421 Jul 10 '15 at 13:52
  • 2. It is better to store the number 1 (AstrologicalSignID) 30 times, instead of Sagittarius. This may not be a big deal because of the size of your project but if you were to scale it to 1,000,000 entries, think about the impact of the size of the data and any indexes that you create. The number of tables should not be of concern to you, generally speaking. You want to make sure that your table are properly normalized and that your table and index sizes are optimized. – user16421 Jul 10 '15 at 14:06
  • Wow, I never thought of the weight of the database index being related to the size of the index and the repetition of a 10+ character long astrological sign as opposed to an integer. You just opened me a huge and shiny door to improve my model :D Thank you ! – user70277 Jul 10 '15 at 15:38
  • No problem. Please vote the answer as useful, if it was. – user16421 Jul 10 '15 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy