1

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
person_id | list_name | list_source

date_of_birth
person_id | year | month | day

name
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!

| improve this question | |
  • 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 Feb 6 '17 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. – Walter Mitty Feb 6 '17 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 – Avidos Feb 6 '17 at 23:59
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.

| improve this answer | |
  • 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 Feb 7 '17 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 Feb 7 '17 at 18:13

Your Answer

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

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