RelationalDBDesign RelationalDBDesign



ER Diagrams   «Prev  Next»
Lesson 9 Many-to-many Relationships (continued)
ObjectiveIdentify many-to-many Relationships

Identify Many-to-many Relationships

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.

  1. The STUDENT and CLASS entities cannot actually be put into a direct relationship, because this is forbidden in a relational database.
  2. The ClassID (key attribute) from CLASS is inserted into STUDENT to form the link.
  3. ClassName attribute is inserted into STUDENT as well, in order to identify the classes in which a student is enrolled (not just through cryptic ClassID, but by ClassName as well).
  4. Assume the two entities have been converted into tables and are populated with data appropriate to each. The STUDENTs table is shown here.
  5. The STUDENTs table reveals a great deal of redundant data. (look at the duplicate entries in the ClassName field alone). Redundant data, you will see, is the source of the problems with many-to-many relationships.


Many-to-Many Relationships

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)
  1. Ai can be related to zero, one, or more instances of entity B and
  2. 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.