Understanding When SQL Databases Need a Third Table

[Rajeev Bagra]

When beginners start learning relational databases, one confusing topic is understanding why some database designs require an extra table while others do not.

A simple way to understand this is by focusing on the relationship between entities.

One-to-Many Relationships

Suppose a database stores information about schools and teachers.

If:

• One school can have many teachers
• But each teacher belongs to only one school

then there is no need for a third table.

The database can simply store the school_id inside the teachers table.

Example:

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

CREATE TABLE teachers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    school_id INTEGER,

    FOREIGN KEY (school_id) REFERENCES schools(id)
);

In this structure:

• One school record can connect to many teacher records
• Each teacher record points to only one school

This is called a one-to-many relationship.

---

Many-to-Many Relationships

Now imagine a different situation:

• A teacher can teach at multiple schools
• A school can have multiple teachers

This changes everything.

A single school_id column inside the teachers table is no longer enough because one teacher may need several school IDs.

That is when a third table becomes necessary.

Example:

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

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

CREATE TABLE school_teachers (
    school_id INTEGER,
    teacher_id INTEGER,

    FOREIGN KEY (school_id) REFERENCES schools(id),
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);

The school_teachers table acts as a bridge between the two entities.

This is called a many-to-many relationship.

---

Real-World Example: Airports

Airports often use many-to-many relationships.

For example:

• One passenger can take many flights
• One flight can contain many passengers

Because of this, a third table such as check_ins or tickets is created to connect passengers and flights.

Example:

CREATE TABLE check_ins (
    passenger_id INTEGER,
    flight_id INTEGER,

    FOREIGN KEY (passenger_id) REFERENCES passengers(id),
    FOREIGN KEY (flight_id) REFERENCES flights(id)
);

---

Simple Rule to Remember

Relationship Type	Third Table Needed?
One-to-One	Usually No
One-to-Many	No
Many-to-Many	Yes

Understanding this single idea helps beginners design cleaner and more scalable databases.

[Author]

Posts