How to Create Relationships between MySQL Tables

By Steve Suehring, Janet Valade

Some tables in a MySQL database are related. Most often, a row in one table is related to several rows in another table. You need a column to connect the related rows in different tables. In many cases, you include a column in one table to hold data that matches data in the primary key column of another table.

A common application that needs a database with two related tables is a customer order application. For example, one table contains the customer information, such as name, address, and phone number. Each customer can have from zero to many orders.

You could store the order information in the table with the customer information, but a new row would be created each time the customer placed an order, and each new row would contain all the customer’s information.

You can much more efficiently store the orders in a separate table, named perhaps CustomerOrder. (You can’t name the table just Order because that’s a reserved word.) In the CustomerOrder table, you include a column that contains the primary key from a row in the Customer table so the order is related to the correct row of the Customer table.

Each customer has a unique cust_id. The related CustomerOrder table is shown here. It has the same cust_id column that appears in the Customer table. Through this column, the order information in the CustomerOrder table is connected to the related customer’s name and phone number in the Customer table.

Sample Data from the CustomerOrder Table
order_no cust_id item_name cost
87-222 27895 T-Shirt 20.00
87-223 27895 Shoes 40.00
87-224 12345 Jeans 35.50
87-225 34521 Jeans 35.50
87-226 27895 Hat 15.00

In this example, the columns that relate the Customer table and the CustomerOrder table have the same name. They could have different names, as long as the columns contain the same data.