One to many relationship

One to many relationship

In a database, a one-to-many relationship exists when each record in Table A has many linked records in Table B, but each record in Table B only has one matching record in Table A.

A One-to-Many Relationship Example

Consider a teacher's relationship with the courses they teach. A teacher can teach many classes, but the relationship between the course and the teacher will differ.

As a result, there could be numerous records in the Courses database for each record in the Teachers table. This is an example of a one-to-many relationship: a single instructor for many courses.

Why Is It Important to Form a One-to-Many Relationship?

At least two tables are required to illustrate a one-to-many relationship. Let's take a look at why.

Adherence to the Design of the First Normal Form

Perhaps we made a table to keep track of the names of the teachers and the courses they taught. We could make a table like this for Teachers and Courses:

Teacher_ID Teacher_Name Course
Teacher_01 Carmen Biology
Teacher_02 Veronica Math
Teacher_03 Jorge English

What if Carmen teaches more than one class? With this design, we have two alternatives. We might include it in Carmen's existing file, as follows:

Teacher_ID Teacher_Name Course
Teacher_01 Carmen Biology, Math
Teacher_02 Veronica Math
Teacher_03 Jorge English

However, the above design is rigid and may cause issues later when inserting, editing, or deleting data. It makes data searching more complex.

This design also violates the First Normal Form (1NF) concept of database normalisation, which specifies that each table cell should contain just one discrete data item.

The Second Rule of Normal Form
Another design option is to include a second record for Carmen.:

Teacher_ID Teacher_Name Course
Teacher_01 Carmen Biology
Teacher_02 Carmen Math
Teacher_03 Veronica Math
Teacher_04 Jorge English

Although this approach follows 1NF, it is still bad database architecture because it introduces redundancy and may unnecessarily bloat a huge database. More crucially, the data may become erratic.
What if Carmen's name was changed, for example? Someone working with the data could change her name in one record but forget to do so in the next. This design violates the Second Normal Form (2NF) standard, which adheres to 1NF while avoiding numerous record redundancies. The 2NF rule accomplishes this by dividing data into subsets and linking them.

How to Design a Database With One-to-Many Relationships

Break the tables into two and link them with a foreign key to create a one-to-many relationship in the Teachers and Courses database

Teacher_ID Teacher_Name
Teacher_01 Carmen
Teacher_02 Veronica
Teacher_03 Jorge

Here's a look at the Courses table. Teacher ID is a foreign key that connects a course to a teacher in the Teachers table:

Course_ID Course_Name Teacher_Name
Course_001 Biology Teacher_01
Course_002 Math Teacher_02
Course_002 English Teacher_03

Using a foreign key, we've created a relationship between the Teachers and the Courses tables. Carmen teaches both Biology and Math, while Jorge teaches English, according to this arrangement.