A classic example of a many-to-many relationship is this:
A student may enroll in many classes (1:M);
a class may have many enrolled students (1:N).
The following Slide Show illustrates the relationship between the STUDENT and CLASS entities and their attributes.
Many-to-many relationships are also very common. There is, for example, a many-to-many relationship between an order placed by an DistributedNetworks customer and the merchandise items carried by the store. An order can contain multiple items, and each item can appear on more than one order. The same is true of the orders placed with distributors. An order can contain multiple items and each item can appear on more than one order.
A many-to-many relationship
exists between entities A and B if for two instances of those entities (Ai and Bi)
- Ai can be related to zero, one, or more instances of entity B and
- Bi can be related to zero, one, or more instances of entity A.
Many-to-many relationships present two major problems to the design of a database.
These issues and the way in which we solve them are discussed later. The next lesson explains the problems with many-to-many relationships.