Managing & Using MySQL, 2nd Edition

Refining Relationships

As we noted earlier, one-to-one relationships are quite rare. In fact, if you encounter one during your data modeling, you should take a closer look at your design. A one-to-one relationship may imply that two entities are really the same and should be folded into a single entity.

Many-to-many relationships are more common than one-to-one relationships. In these relationships, there is often some data we want to capture about the relationship. For example, take a look at the earlier version of our data model in Figure 7-8 that had the many-to-many relationship between Artist and CD. What data might we want to capture about that relationship? An Artist has a relationship with a CD because an artist has one or more songs on that CD. The data model in Figure 7-9 is actually another representation of this many-to-many relationship.

All many-to-many relationships should be resolved using the following technique:

1. Create a new entity (sometimes referred to as a junction entity ). Name it appropriately. If you cannot think of an appropriate name for the junction entity, name it by combining the names of the two related entities (e.g., ArtistCD). In our data model, Song is a junction entity for the Artist/CD relationship.

2. Relate the new entity to the two original entities. Each of the original entities should have a one-to-many relationship with the junction entity.

3. If the new entity does not have an obvious unique identifier, inherit the identifying attributes from ...