Linking tables with foreign keys
When I started designing databases I had a tendency to try to store data that seemed "related" in one table. I might, for example, try to store orders in a field in the customers table. Because orders "belong" to a customer, right? No. Customers and orders represent separate entitites in a database. They both need their own table. And the records in these two tables can be linked to establish the relationship between customers and orders. Designing a database is a matter of deciding what the entities are you want to store and which relationship exists between them.
Customers and orders are related to eachother in a one-to-many relationship, because, a (one) customer can have many orders and each (many) order is placed by exactly one customer. Don't worry if this is a bit vague at this moment. We will talk more about database relationships in the next pages.
What is important now is that a one-to-many relationship requires the use of two separate tables. One for customers and one for orders. Lets practice a bit by creating these tables.
Deciding what data to store
First, we should decide what information to store about customers and orders. In order to do this, we must ask ourselves the question
"What single pieces of information belong to a customer?" and "What single pieces of information belong to an order?"
Designing the customer table
Orders do belong to a customer, but they are not a single piece of information. The fields below are all single pieces of information that belong to a customer.
- customer_id (primary key)
Let's actually create the table in Sqlyog. Below is an example of what this table would look like when you created it in "new table" window of Sqlyog. All graphical database management tools for all different databases have similar interfaces for creating tables. And you can also create a table with SQL, without using a graphical database management tool.
Creating a new customer table in Sqlyog. Notice that the PK (primary key) checkbox is checked for the customer_id field. The customer_id is the primary key. Also, the Auto Incr box is checked, meaning that the database will autmatically provide a unique (incrementing) value for this field.
Designing the orders table
What single pieces of information belong to an order?
- The order_id (primary key)
- The date and time the order was placed
- The customer the order was placed by
Below is again an example of what this table would look like in the "new table" windows of Sqlyog.
The design of the order table. The customer field is a reference (a 'foreign key') to the customer_id from the customer table.
These two tables are linked, because the customer field in the order table is a reference to the primary key (customer_id) of the customer table. Such a reference is called a foreign key relationship. You should see a foreign key as a copy of the primary key of another table. In this case, the customer_id from the customer table is copied to the order table so each order gets linked to a customer.
Creating the foreign key relationship
You may wonder how you can see that the customer field in the order table references the customer_id field in the customer table. You can't, because I didn't show you how I created the relationship yet.
Below is the Sqlyog window that I used to create foreign key relationships between tables.
Creating a foreign key relationship between the customer and order tables.
In the window above you can see how the customer column from the order table on the left is linked to the customer_id primary key of the customer table on the right.
Now, when you look at the data that could be present in the customer and order tables, you will see how customers and orders are linked.
Orders are linked to a customers via the customer field that references the customer table.
From the data above you can see that customer mary placed three orders, customer pablo placed one, and customer john placed no orders yet.
You might be wondering WHAT exactly these people ordered. That's a good question. You might have expected to see ordered products in the order table. That would be bad design. How would you stick multiple products in a single order record? Products are separate entities that must be stored in a separate table. The relationship between orders and products is a many-to-many relationship. We will get to that in the next pages.