One-to-Many relationship in Database

Learn how this fundamental concept organizes data hierarchically, empowering efficient data management and retrieval. Discover best practices and examples for implementing and optimizing one-to-many relationships in your database designs.

To be more specific when each record in a table can have multiple related records in another table.

In the following scenarios we can establish one-to-many relationship in the database -

• Users can place multiple orders
• Students can study multiple subjects
• Teacher teaches multiple subjects
• The library has multiple books
• Users can have multiple subscriptions etc.

ER Diagram

Tables

If we create tables based on this ER diagram then we will get three tables.

Here we will use natural keys for all the tables.

In the teacher table teacher_id is PRIMARY KEY and in the subject table subject_id is PRIMARY KEY.

In the teaches table teacher_id and subject_id are FOREIGN KEY to the teacher and subject table respectively.

Create the teacher table with all required columns

CREATE TABLE teacher (
  teacher_id varchar(8) PRIMARY KEY,
  name varchar(16),
  city varchar(16)
)

Create the student table with all required columns

CREATE TABLE subject (
  subject_id varchar(8) PRIMARY KEY,
  name varchar(16)
)

Then create the teaches table to maintain the relationship between both the tables.

For the table, we have to decide what will the primary key.

It's not possible to use teacher_id as the primary key because its value will be repeated since one teacher can teach multiple subjects but subject_id will always be unique because one subject will be taught by one teacher at a time.

• So, we can choose subject_id as the PRIMARY KEY for this table after this is marked as PRIMARY KEY we are sure this column will never have duplicate values.
• Another option is to use surrogate keys as PRIMARY KEY i.e., use AUTO INCREMENTING integers as PRIMARY KEY by adding a new column.
• Another option is to use composite keys by marking both teacher_id and subject_id as primary keys.

These are all possibilities for choosing PRIMARY KEY for the teaches table. Here we will choose subject_id as PRIMARY KEY but other options can also be used.

CREATE TABLE teaches (
  teacher_id varchar(8),
  subject_id varchar(8) PRIMARY KEY,
  FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id),
  FOREIGN KEY (subject_id) REFERENCES subject(subject_id)
);

In case you want to use AUTO INCREMENTING keys then use the following -

CREATE TABLE teaches (
  id int AUTO_INCREMENT PRIMARY KEY,
  teacher_id varchar(8),
  subject_id varchar(8) UNIQUE,
  FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id),
  FOREIGN KEY (subject_id) REFERENCES subject(subject_id)
);

In the case of using AUTO INCREMENTING integers as PRIMARY KEY mark subject_id as UNIQUE otherwise each subject will be allowed to be taught by multiple teachers since we allowed duplicate entries and it will work as many to many relationship.

Always keep data type of FOREIGN KEY column same as in the base table but column name can be changed in referencing table.

Reducing the tables

In the current design let's see if it is possible to reduce the table or optimise the design of schemas.

We already have the teacher_id column in the teacher table and the subject_id column in the subject table so we are repeating these two columns. Instead of repeating we can use those columns may be.

We cannot reuse the teacher_id column from the teacher table because we have to maintain repeated values for teacher_id since each teacher can teach multiple subjects.

But in the teaches table subject_id column is always unique and it is always unique in the subject table so let's merge these two.

Or we can understand the relation better by this image

If we remove the teaches table and add a new column to the subject table then we can keep a record of teachers teaching a particular subject.

Added teacher_id, a new column as FOREIGN KEY referencing teacher table.

Hence we reduced/optimized the tables.

In one to many relationship we can always remove the intermediate relationship table and move to ONE table's unique primary key into MANY table's as a new column as FOREIGN KEY.

Let's see the SQL code to generate these tables.

CREATE TABLE teacher (
	teacher_id varchar(8) PRIMARY KEY,
	name varchar(16),
	city varchar(16)
)

CREATE TABLE subject (
    subject_id varchar(8) PRIMARY KEY,
    name varchar(16),
    teacher_id varchar(8),
    FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);

This is how we can establish one-to-many relationship in a relational database.