Mysql – Using the same primary key as a foreign key for every table

database-designMySQLperformance

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!

Best Answer

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.

Related Solutions

Set the primary key for many to many relationship

In a practical system (as opposed to a textbook problem) you would want to have some kind of transaction identifier on any transaction like hiring one or more pieces of equipment. Assuming you are running an equipment hiring business, your transactions would probably have to account for letting out multiple pieces of equipment at once. Therefore you need to add an order table as follows:

Table Order
order_id
order_date

and your Hire_Equipment table would change as follows:

Table Hire_Equipment
customer_id (PK)
order_id (PK)
equip_id (PK)
expected_return_date

Note that the hiring_date has been normalised up to the Order level, but expected_return_date has been left at the item level, since you might want to allow a single order to contain a mixture of rental periods.

This design allows a customer to hire a piece of equipment more than once on a given date but it prevents hiring the same equipment more than once on the same order - which is what you would expect.

Mysql – using foreign key as primary key

Primary keys must be unique. As long as any particular user/user_id can't become more than a single seller/buyer (each), you will be fine. Whether or not that restriction does hold, depends on your domain. It would, e.g., fail, if a user can run multiple shops with each shop's owner being (considered) a distinct seller. (You might, e.g., want to attach contact information to a seller, which could be different from shop to shop - but specific to the seller, not to the shop. Just think of co-owned businesses…) In case of doubt, you might want to play safe, and not make the foreign key the primary key to save you from the trouble of propagating a redesign through your schema some time in the future.

As for MySQL trying to attempt to fill in the gaps: If it is a foreign key, you(r application) will be the only one to insert records. Thus: No.

Related Question