Why a `school_companies` Table Is Usually Unnecessary in Database Design

[Rajeev Bagra]

A beginner learning relational database design (project: https://cs50.harvard.edu/sql/psets/2/connect/) often starts thinking:

Should I create a table connecting schools and companies?

At first, this can feel logical because:

• people attend schools
• people work at companies
• schools and companies seem indirectly related

However, an important database design principle is:

Create relationship tables only when the application truly requires a direct relationship between two entities.

In the CS50 SQL “Happy to Connect” project, the actual relationships are:

• people ↔ schools
• people ↔ companies
• people ↔ people (connections)

This means the database naturally becomes:

people
schools
companies
people_schools
people_companies
connections

Notice something important:

• schools are connected to people
• companies are connected to people
• but schools are not directly connected to companies

A useful way to think about this is:

The person acts as the bridge between schools and companies.

For example:

• Raj studied at Harvard
• Raj later worked at Google

The relationship already exists naturally through the person:

Harvard ← Raj → Google

Because of this, creating another table like:

school_companies

would often introduce:

• unnecessary complexity
• duplicate relationship data
• confusing database structure

Good relational database design aims to:

• avoid redundancy
• store only meaningful relationships
• keep schemas normalized

A good rule of thumb is:

Only create a junction table when both entities truly need a direct many-to-many relationship.

Examples where junction tables are necessary:

people ↔ schools
people ↔ companies
students ↔ courses
authors ↔ books

Examples where they may not be necessary:

schools ↔ companies

unless the application specifically tracks:

• recruitment partnerships
• campus hiring programs
• official collaborations

This is one of the key ideas behind normalization in relational databases:

Store relationships only where they are meaningful and required by the system.

[Author]

Posts