Cover by Robin Nixon

Safari, the world’s most comprehensive technology and business learning platform.

Find the exact information you need to solve a problem on the fly, or go deeper to master the technologies and skills you need to succeed

Start Free Trial

No credit card required

O'Reilly logo

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 these relationships.

One-to-One

A one-to-one relationship between two types of data 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 only ever be one customer at any given 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.

The Customers table, , split into two tables

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

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.

Of course, ...

Find the exact information you need to solve a problem on the fly, or go deeper to master the technologies and skills you need to succeed

Start Free Trial

No credit card required