Many-to-Many Relationship

What Does Many-to-Many Relationship Mean?

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.

Advertisements

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

Techopedia Explains Many-to-Many Relationship

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.

In a database, such a relationship has to be broken down into intermediary tables. In our school example, this may take the form of a third table that contains the columns “Student_ID”, “Subject_ID”, “Semester” and “Year”. For each student who takes a certain subject in a certain semester in a certain year, a row can be entered into this table. The Student_ID is read from the Student master table, while the Subject_ID is read from the Subject master table. In this way, many students can be related to many subjects.

Another example can be a relationship between employees and departments. Each department can have any number of employees working on a specific task. Similarly, an employee can also work for multiple departments at one time. Therefore, any number of departments or employees can simultaneously be linked to each other by creating an intermediary table that links them using the Employee_ID and the Department_ID.

Advertisements

Related Terms

Margaret Rouse
Technology Expert

Margaret is an award-winning technical writer and teacher known for her ability to explain complex technical subjects to a non-technical business audience. Over the past twenty years, her IT definitions have been published by Que in an encyclopedia of technology terms and cited in articles by the New York Times, Time Magazine, USA Today, ZDNet, PC Magazine, and Discovery Magazine. She joined Techopedia in 2011. Margaret's idea of a fun day is helping IT and business professionals learn to speak each other’s highly specialized languages.