A one-to-one relationship says that the two things are matched exactly.The relationship between ship and captain is one-to-one. Each ship has one captain, and each captain has one ship (ignoring real world details like mutinous ships, captains waiting for a command, etc.).

As you might guess, a one-to-many relationship means that one thing in this table corresponds to potentially many things in that table. Each individual ship has multiple sailors, so the ship/crew member relationship is one-to-many. All the sailors on a given ship will have the same value for the “belongs to the crew of” attribute. The “one” side of a one-to-many relationship will be a primary key, as shown in Figure 23-1. The ship's name would be a primary key in the table of a shipping line's fleet.

Figure 23-1. Breaking up a many-to-many relationship: “Many people listen to many bands”

Many-to-many relationships occur when multiple records in one table are somehow related to multiple records in another table. We can see what this means if we introduce a “MusicGroup” table that is a list of bands. We could store any band-specific information in it too, such as the land of origin for each music group. Take, for example, Table 23-4.

Now our database has tables that hold a many-to-many relationship. Some people listen to several bands, and some bands are listened to by several people. That's a many-to-many relationship between the Person and MusicGroup tables.

Many-to-many relationships can't be processed directly in relational databases (though it's clearly possible to create the table). The reason is that a primary key can only link tables on a one-to-many basis. Unless you take other steps, the restriction on many-to-many means we cannot make direct queries based on band name (e.g., “who listens to a given band?”). That may be acceptable if you never want to make that kind of query, but you want to build flexibility into your designs, do not rule it out.

Stating the many-to-many limitation in terms of Java code, you can think of a primary key as being like the index variable in a Java “for” loop. It lets you process the whole table without missing any rows out, or considering any primary keys twice. Many-to-many would be like resetting the index variable several times in the looping. Luckily, there is an easy way to get over the restriction that many-to-many relationships can't be processed directly. You resolve many-to-many relationships by adding a new table that can express the relationship in terms of two one-to-many tables.

We simply need a new table that, for each band, has a record for each person who listens to it. That new table is on the “many” end of a one-to-many relationship with the MusicGroup table. The new table must also have for each person, a record for each band they listen to. So the new table will also be on the “many” end of a one-to-many relationship with the Person table. And that's an exact description of our existing ListensTo table! (Of course, the design was chosen with this in mind.) Each name there is related to several music group names, and each music group is related to several names. You can see that Robert is associated with the Beatles, Abba, and Oasis, while Oasis is associated with Timothy and Robert.

Using the ListensTo table we represent the many-to-many relationship between MusicGroup and Person. We can now do the SQL equivalent of “for each MusicGroupName in MusicGroup table, find the matching MusicGroupName in the ListensTo table, and print out the person name.” This new table allows us to do queries by band.

To summarize, we resolve many-to-many relationships in a relational database by decomposing them into two one-to-many relationships, adding a new table as needed.