I'm trying to design a database to hold information that is compiled from different lists. I'd like to record each person listed and assign an ID but due to the nature of the lists and their lack of accuracy, a person could be listed as having multiple names, aliases or misspellings, also they may have no date of birth listed or multiple dates of birth. There are about 8 different attributes that can have no information for one person person can have multiple records for another person.

My question is.. if I have one table that lists the person's ID and where I got the information, would it make sense to have 8 other tables that use that ID as a foreign and primary key and also have columns such as the date(s) of birth associated with that person? Or is this overusing the primary key from the other table?

For example

person_id | list_name | list_source

person_id | year | month | day

person_id | first | middle | last

I'm very new to all of this and don't know enough to even start my google search on different methods of setting up a database or what a database of this type would be called (if that's a thing?) so even proper terminology for what I'm doing what be appreciated!

  • That's perfectly legitimate. Depending on how you're going to use the data, it might also be meaningful to have all the columns in just one table (person) and use null values for unknown or not applicable values (as far as you use nulls in a consistent and documented manner).
    – joanolo
    Commented Feb 6, 2017 at 22:59
  • There is a technique called Shared Primary Key that is almost identical to what you have shown. You should look it up, especially over in Stackoverflow, where there is a tag with that name. Commented Feb 6, 2017 at 23:53
  • Depending on how you normalize the structure based on the requirement, check the documentation and consult the Application Developer so you're both on the same side. Also, maybe this can help you: studytonight.com/dbms/database-normalization.php Commented Feb 6, 2017 at 23:59

1 Answer 1


In general it is bad schema design to have two tables in a 1:1 relationship. (There are exceptions, but yours is not one.)

In general, birthdate should be a single column of type DATE, not 3 columns.

  • Are my tables considered 1:1? Since each person may have 1 to 10 amount of names and none to 10 dates of birth I assumed this was a 1 to many relationship. Also, some of the birthdates only have a year, should I put the full date in a single column and is there a way to query only the dates of a certain year still?
    – alycoh12
    Commented Feb 7, 2017 at 14:19
  • One person can have only one birthdate, unless you have a strange definition of "person". If you need to store multiple names, then, yes, it is 1:many, and needs to be in a separate table. As for incomplete dates... Maybe just a string?
    – Rick James
    Commented Feb 7, 2017 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.