Learning PHP, MySQL, and JavaScript

Relationships

MySQL is called a relational database management system because its tables store not only data but the relationships among the data. There are three categories of relationships.

One-to-One

A one-to-one relationship is like a (traditional) marriage: each item has a relationship to only one item of the other type. This is surprisingly rare. For instance, an author can write multiple books, a book can have multiple authors, and even an address can be associated with multiple customers. Perhaps the best example in this chapter so far of a one-to-one relationship is the relationship between the name of a state and its two-character abbreviation.

However, for the sake of argument, let’s assume that there can ever be only one customer at any address. In such a case, the Customers-Addresses relationship in Figure 9-1 is a one-to-one relationship: only one customer lives at each address and each address can have only one customer.

Usually, when two items have a one-to-one relationship, you just include them as columns in the same table. There are two reasons for splitting them into separate tables:

• You want to be prepared in case the relationship changes later.

• The table has a lot of columns and you think that performance or maintenance would be improved by splitting it.

Figure 9-1. The Customers table, Table 9-8, split into two tables

Of course, when you come to build your own databases ...