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.
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, when you come to build your own databases in the real
world, you will have to create one-to-many Customer-Address relationships
(one address, many
customers.)
One-to-Many
One-to-many (or many-to-one) relationships occur when one row in one table is linked to many rows in another table. You have already seen how Table 9-8 would take on a one-to-many relationship if multiple customers were allowed at the same address, which is why it would have to be split up if that were the case.
So, looking at Table 9-8a within Figure 9-1, you can see that
it shares a one-to-many relationship with Table 9-7 because there is only one of each
customer in Table 9-8a.
However, Table 9-7, the Purchases table, can (and does) contain more
than one purchase from a single customer. Therefore,
one customer can have a relationship with
many purchases.
You can see these two tables alongside each other in Figure 9-2, where the dashed lines joining rows in each table start from a single row in the lefthand table but can connect to more than one row in the righthand table. This one-to-many relationship is also the preferred scheme to use when describing a many-to-one relationship, in which case you would swap the left and right tables to view them as a one-to-many relationship.
Many-to-Many
In a many-to-many relationship, many rows in one table are linked to many rows in another table. To create this relationship, add a third table containing a column from each of the other tables with which they can be connected. This third table contains nothing else, as its sole purpose is to link up the other tables.
Table 9-12 is just such a table. It
was extracted from Table 9-7, the
Purchases table, but omits the
purchase date information. It contains a copy of the ISBN number of
every title sold, along with the customer number of each
purchaser.
Customer | ISBN |
1 | 0596101015 |
2 | 0596527403 |
2 | 0596101015 |
3 | 0596005436 |
4 | 0596006815 |
With this intermediary table in place, you can traverse all the information in the database through a series of relations. You can take an address as a starting point and find out the authors of any books purchased by the customer living at that address.
For example, let’s suppose that you want to find out about purchases in the 23219 zip code. Look up that zip code in Table 9-8b, and you’ll find that customer number 2 has bought at least one item from the database. At this point, you can use Table 9-8a to find out the customer’s name, or use the new intermediary Table 9-12 to see the book(s) purchased.
From here, you will find that two titles were purchased, and you can follow them back to Table 9-4 to find the titles and prices of these books, or to Table 9-3 to see who the authors were.
If it seems to you that this is really combining multiple one-to-many relationships, then you are absolutely correct. To illustrate, Figure 9-3 brings three tables together.
Follow any zip code in the lefthand table to the associated customer IDs. From there, you can link to the middle table, which joins the left and right tables by linking customer IDs and ISBN numbers. Now all you have to do is follow an ISBN over to the righthand table to see which book it relates to.
You can also use the intermediary table to work your way backward
from book titles to zip codes. The Titles table can tell you the ISBNs, which you
can use in the middle table to find the ID numbers of customers who
bought the books; finally, the Customers table matches the customer ID
numbers to the customers’ zip codes.
Databases and Anonymity
An interesting aspect of using relations is that you can accumulate a lot of information about some item—such as a customer—without actually knowing who that customer is. In the previous example, note that we went from customers’ zip codes to customers’ purchases and back again, without finding out the customers’ names. Databases can be used to track people, but they can also be used to help preserve people’s privacy while still finding useful information.