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,Songis a junction entity for theArtist/CDrelationship.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 the original entities into the junction entity and use them together as the unique identifier for the new entity.
In almost all cases, you will find additional attributes that belong in the new junction entity. In any case, the many-to-many relationship needs to be resolved; otherwise, you will have a problem translating your data model into a physical schema.