Relationships

Now that you know how to create database tables and manipulate data, it'stime to take a look at the principles of relational databases and see how to apply good design practices to your own databases.

Data in a relational database is split across several tables in a structured way. Relationships are the rules that determine how data in one table is related to data in another. When you join tables in SQL, you specify the relationship between the tables in the query. Refer to Lesson 11, "Joining Tables," for more information on joins.

Consider the relationship between two tables in the sample database. The orders table is related to customers. Each record in the orders table contains a customer_id, which is a foreign key column that corresponds to the primary key in customers.

Therelationship between customers and orders is known as a one-to-many relationship because one row in the customers table can be related to many rows in the orders table. Think of what the database is trying to achieve herea customer must not be limited to placing only one order!

Many-to-One

A one-to-many relationship could be considered a many-to-one relationship, depending on which way you look at it. You could think of the relationship between products and customers as many-to-one.

The relationship between products and orders is a many-to-many relationship. An order can contain the same product many timesalthough the quantity column should help to avoid the need to do thisand, of course, each product can appear on many orders.

In the sample table, the many-to-many relationship between products and orders is actually implemented using two separate relationships and the order_lines table. There is a one-to-many relationship between orders and order_lines, and also a one-to-many relationship between products and order_lines.

As you might expect, a one-to-one relationship is also possible, although there is no relationship of this kind in the sample database. A one-to-one relationship uses a key that is unique in both tables; therefore, the data could actually be stored in one table instead of two.

Usually you use a one-to-one relationship for performance reasons. For example, you could keep one table highly optimized by storing only the most commonly used columns. The second table would contain larger amounts of data and would be joined to the first only when those columns were required. In particular, you can often improve performance by separating large text or binary data columns from the body of a table using a one-to-one relationship.