Back to course

Relationship Modeling: One-to-Many (1:N)

Database Fundamentals: From Zero to Hero

Lesson 10: Relationship Modeling: One-to-Many (1:N)

This is the most common type of relationship in relational databases and is the foundation for avoiding data redundancy.

Defining a One-to-Many Relationship

A 1:N relationship means:

  • One record in the 'parent' table (the 'One' side) can relate to many records in the 'child' table (the 'Many' side).
  • Each record in the 'child' table can only relate to one record in the 'parent' table.

Implementation of 1:N

We enforce this relationship by placing the Primary Key of the 'One' side into the 'Many' side as a Foreign Key.

Example: Departments and Employees

  • A Department (Parent) can have many Employees (Children).
  • An Employee (Child) can belong to only one Department (Parent).
  1. Parent Table (Departments): PK = DeptID
  2. Child Table (Employees): FK = DeptID

sql -- 1. Create the 'One' table (Parent) CREATE TABLE Departments ( DeptID INTEGER PRIMARY KEY, DeptName VARCHAR(50) );

-- 2. Create the 'Many' table (Child) CREATE TABLE Employees ( EmployeeID INTEGER PRIMARY KEY, Name VARCHAR(100), DeptID INTEGER, -- FK FOREIGN KEY (DeptID) REFERENCES Departments(DeptID) );

If we have DeptID 10 ('Marketing'), we can have 100 employees all referencing DeptID 10. The 100 employees share the department data without duplicating the department name 100 times.