Give an example of a many-to-many relationship and explain its unsuitability for a relational database.

Question

Designing a database requires both a logical design and a physical design. The logical design models the database from a business perspective. The organization's data model should reflect its key business processes and decision-making requirements. The process of creating small, stable, flexible, and adaptive data structures from complex groups of data when designing a relational database is termed “normalization.” A well-designed relational database will not have many-to-many relationships, and all attributes for a specific entity will only apply to that entity. Give an example of a many-to-many relationship and explain its unsuitability for a relational database.

Answer 1

A many-to-many relationship refers to a relationship between tables in a database when a parent row in one table contains several child rows in the second table, and vice versa. Many-to-many relationships are often tricky to represent.

The many-to-many relationship is usually a mirror of the real-life relationship between the objects the two tables represent.

A database used by a school application can be taken as an example. Two of the tables it contains are "Student" and "Subject." In real life, a student will take several subjects simultaneously, while a subject will be studied by several students at a time. This is a many-to-many relationship.

Relation databases have redundancy which may be lost in many-to-many relationship.