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:

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:

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.:

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

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:

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.