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:
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 theArtist
/CD
relationship.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.
If the new entity does not have an obvious unique identifier, inherit the identifying attributes from ...
Get Managing & Using MySQL, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.