One-to-Many Relationships

Imagine you're running a school cafeteria where students can choose their favorite juice boxes each day. Some students like to enjoy multiple juice boxes, but each juice box belongs to only one student. This scenario perfectly illustrates a one-to-many relationship in a database.

What is a One-to-Many Relationship?

A one-to-many relationship means that one entity from one table can be associated with one or more entities in another table. In our cafeteria example:

• One student can have many juice boxes.
• Each juice box belongs to only one student.

How to Specify a One-to-Many Relationship

Let’s explore this relationship through four approaches:

1. Scenario description
2. Sample data
3. Schema diagram
4. SQL commands

1. Scenario Description

Each student in the school is assigned a unique ID and can select one or more juice boxes to accompany their snacks. Each juice box is identified by its own unique ID and is linked to the student who chose it.

2. Sample Data

Writing down examples of the data helps clarify the relationship. Here’s a small portion of the data:

students table:

id   name
1    Alim
2    Andrei
3    Belma
    

juice_boxes table:

id   juice         date          student_id
111  apple         2020-01-22   1
112  apple         2020-01-22   2
113  orange        2020-01-22   3
114  apple         2020-01-23   1
115  orange        2020-01-23   1
116  peach-mango   2020-01-23   2
117  grape         2020-01-23   3
    

In this data, you can see which student drank which juice on specific days. For example:

• Alim (ID 1) drank two juice boxes (IDs 114 and 115) on January 23, 2020.
• Belma (ID 3) drank an orange juice box (ID 113) on January 22, 2020, and a grape juice box (ID 117) on January 23, 2020.

3. Schema and Schema Diagram

A schema describes how the database is structured. Here are the schemas for the students and juice_boxes tables:

students table schema:

column       type        constraints
id           INTEGER     PRIMARY KEY
name         TEXT
    

juice_boxes table schema:

column       type        constraints
id           INTEGER     PRIMARY KEY
juice        TEXT
date         TEXT
student_id   INTEGER     FOREIGN KEY REFERENCES students(id)
    

The schema diagram visually represents the relationship:

• 1: The students table contains one row per student.
• *: The juice_boxes table contains multiple rows referencing the same student.

4. SQL Commands

To create these tables and establish the one-to-many relationship in SQLite3, use the following SQL:

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE juice_boxes (
  id INTEGER PRIMARY KEY,
  juice TEXT,
  date TEXT,
  student_id INTEGER,
  FOREIGN KEY (student_id) REFERENCES students(id)
);
    

In this SQL:

• The student_id column in the juice_boxes table serves as a FOREIGN KEY.
• The FOREIGN KEY references the id column in the students table, establishing the relationship.

What You've Learned

You’ve learned to identify and specify a one-to-many relationship in four ways:

• Scenario description: Use real-world scenarios to explain the relationship.
• Sample data: Write down data examples to visualize the relationship.
• Schema diagram: Use diagrams to represent the database structure and relationships.
• SQL commands: Implement the relationship using FOREIGN KEY constraints.

Real-World Applications

One-to-many relationships are common in many systems, including:

• E-commerce: A customer (one) can place multiple orders (many).
• Education: A teacher (one) can teach many students (many).
• Library systems: A borrower (one) can check out multiple books (many).

Understanding one-to-many relationships is crucial for designing efficient and logical databases. Practice creating these relationships in different scenarios to solidify your understanding.