Many-to-many relationships are disallowed in a relational database because of the problems they create. These include:
- Data redundancy
- Data insertion, deletion, and updating difficulties
Refer again to the Students table below, so that these problems can be put into perspective.
Note the redundant data
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.