MySQL - relationships: one to many

What is a one to many relationship?

When it comes to data one database table can use information from another. This is what we mean when we say relationship. A one to many relationship means that one entity or table can have a relationship with multiple other entities. In a real world situation, for example, a customer can have many orders but an order can only belong to the one customer who ordered it.

Why not store all the data in one table?

Using multiple tables avoids storing the same data twice and some NULL values.

For example if you wanted to store a customers first name, last name and email, along with the date of the purchase and the amount it cost, you would be storing the customers details (first name, last name and email) every time they placed an order, if it was all in one table. You would also have NULL data in the date of the purchase and the amount columns if a customer has signed up but hasn't ordered anything.

Having a customer table and an orders table means the customer information will only be stored once and there wouldn't be any NULL values for customers who haven't ordered, in the orders table. This is a one to many relationship between the customer table and the order table.

Storing data across multiple tables means, not only that you won't have duplicating data or NULL values, but it also makes the data more readable.

How does one table know which other table is being referenced?

Relationships between tables are usually made using id's. Going back to the customer and orders example, the customer table would have a customer_id and the orders table would also have a customer_id and one would correspond to the other.

The customer_ids on the customer table are primary keys, and are therefore unique, and correspond to only one user/customer.

The customer_ids on the orders table are foreign keys. A foreign key is a reference to another table within a given table and will enforce that whatever key is being referenced as a foreign key, actually has a corresponding primary key within another table.