When a One-to-Many Turns into a Many-to-Many
- Sometimes, a one-to-many relationship can evolve into a many-to-many relationship as business requirements change.
- For example, consider a Teacher table and a Class table where initially each teacher is assigned to one class (one-to-many).
- If the requirement changes so that teachers can teach multiple classes and classes can have multiple teachers this becomes a many-to-many relationship.
Adjusting the Database Design
To handle this change, we introduce a junction table to maintain the many-to-many relationship.
- Teacher
- TeacherID (Primary Key): Unique identifier for each teacher.
- TeacherName: Name of the teacher.
- Class
- ClassID (Primary Key): Unique identifier for each class.
- ClassName: Name of the class.
- TeacherClass
- TeacherClassID (Primary Key): Unique identifier for each teacher-class assignment.
- TeacherID (Foreign Key): Reference to the TeacherID in the Teacher table, indicating the teacher assigned to the class.
- ClassID (Foreign Key): Reference to the ClassID in the Class table, indicating the class to which the teacher is assigned.
Implementation in terms of SQL Code:
CREATE TABLE Teacher (
TeacherID INT PRIMARY KEY,
TeacherName VARCHAR(50)
);
CREATE TABLE Class (
ClassID INT PRIMARY KEY,
ClassName VARCHAR(50)
);
CREATE TABLE TeacherClass (
TeacherClassID INT PRIMARY KEY,
TeacherID INT,
ClassID INT,
FOREIGN KEY (TeacherID) REFERENCES Teacher(TeacherID),
FOREIGN KEY (ClassID) REFERENCES Class(ClassID)
);
Understanding Relationships in Database Design
In database design, understanding the different types of relationships between data entities is important for creating efficient and effective databases. These relationships define how data in one table relates to data in another, influencing how data is structured, stored and retrieved. The three most common types of relationships are one-to-one, one-to-many and many-to-many.
In this article, We will learn about each Type of Relationship with the example and implementation in detail.