ER Diagrams   «Prev  Next»

Lesson 10Problems with many-to-many Relationships
ObjectiveDescribe the Problems with many-to-many Relationships

Problems with many-to-many Relationships

Many-to-many relationships are disallowed in a relational database because of the problems they create. These include:
  1. Data redundancy
  2. Data insertion, deletion, and updating difficulties

Refer again to the Students table below, so that these problems can be put into perspective.

Students table consisting of StudID, LastName, FIrstName, ClassID, ClassName

Note the redundant data[1] in the ClassID field. Similarly, the ClassName field reveals the same classes listed several times. In a university database with a few thousand students taking four or five courses each, this would produce an enormous amount of redundant data in the ClassName field of the Students table.
Why is this a problem? Suppose a new class-naming policy came out and you were required to modify the names of classes (for example, change Spanish I to Introductory Spanish, and change Spanish II to Intermediate Spanish).
First, you would have to be sure to modify the names in both tables, a minor task in the Classes table. But then, you would have to find every instance of both Spanish classes in the Students table and rename them as well, which is not a minor task at all.
Inserting and deleting new records is also problematic because of the duplicate fields in the Students and Classes tables (ClassID and ClassName). With duplicate fields, the same data has to be maintained across both tables.
Data redundancy and the problems it brings disappear when many-to-many relationships are properly resolved.
The next lesson describes how.
[1]redundant data: Duplicate data in a database; although redundant data can never be totally removed, a major goal of database design is to eliminate as much redundant data as possible.