One-to-Many Relationship Model

In a "class roster" database, a teacher may teach zero or more classes, while a class is taught by one (and only one) teacher. In a "company" database, a manager manages zero or more employees, while an employee is managed by one (and only one) manager. In a "product sales" database, a customer may place many orders, while an order is placed by one particular customer. This kind of relationship is known as one-to-many.

Why One-to-Many Cannot Be Represented in a Single Table

If we begin with a table called Teachers and try to store classes taught, we face the problem of how many class columns to create (class1, class2, class3...). If we begin with a table called Classes and add teacher information, the teacher's data gets duplicated across many rows since one teacher teaches many classes. Neither approach works well in a single table.

Designing Two Tables for One-to-Many Relationship

To support a one-to-many relationship, we design two tables − a Teachers table (parent, the "one" end) with teacherID as primary key, and a Classes table (child, the "many" end) with classID as primary key. The relationship is created by storing the parent's primary key (teacherID) as a foreign key in the child table ?

Foreign Key Concept

The column teacherID in the child table Classes is known as the foreign key. A foreign key of a child table is a primary key of a parent table, used to reference the parent table. This establishes the relationship between the two tables and ensures data integrity while avoiding redundancy.

Conclusion

The one-to-many relationship is the most common relationship type in relational database design. It requires two tables − a parent table (the "one" side) and a child table (the "many" side) linked by a foreign key. This approach efficiently handles scenarios where one entity is associated with multiple instances of another while maintaining data consistency and reducing storage redundancy.