SQL Table Relationships: One-to-Many and Many-to-Many

The word "relational" in "relational database" isn't just marketing. It's the core idea: data lives in separate tables, and those tables are related to each other. Get the relationships right, and your database is easy to query, maintain, and extend. Get them wrong, and you'll be fighting your schema for years.

There are three types of table relationships. In practice, two of them do almost all the work: one-to-many and many-to-many. Let's understand both with interactive examples, and see exactly what they look like in SQL.

What Makes Tables "Related"?

Before we get to the types, let's be clear on the mechanism. Tables are related using foreign keys — a column in one table that holds the primary key of a row in another table.

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,   -- this is a foreign key
    total       REAL    NOT NULL
);

The customer_id column in orders points to a row in customers. That link is the relationship. Simple in concept, powerful in practice.

One-to-One Relationships

The simplest type: one row in Table A corresponds to exactly one row in Table B, and vice versa.

A common example is splitting a users table into users (login info) and user_profiles (personal details). This is done for performance (you only load profile data when you need it) or security (separating sensitive columns).

One-to-one relationships are relatively rare. In most cases, you'd just put the columns directly in the same table. Use one-to-one splitting specifically when you have a clear performance or access-control reason.

One-to-Many Relationships

This is the most common relationship in any database. One row in Table A can relate to many rows in Table B. The reverse is not true — each row in Table B relates to exactly one row in Table A.

Classic examples:

• One customer has many orders
• One category has many products
• One author has many posts
• One department has many employees

The foreign key always lives in the "many" side of the relationship:

customer_id lives in orders (the "many" side). A customer can have zero or many orders, but each order belongs to exactly one customer.

Let's see this in practice:

Notice we use a LEFT JOIN here rather than an INNER JOIN. That's so Dan — who has no orders — still appears in the results with order_count = 0, rather than being silently dropped. When you want to include all rows from the parent (customers) table, use LEFT JOIN.

[!TIP] Rule of thumb: The foreign key always belongs on the "many" side. If you're unsure, ask: "Can one [A] have many [B]?" If yes, the foreign key goes in [B].

Many-to-Many Relationships

Things get more interesting when the relationship goes both ways:

• A student can enroll in many courses, and a course can have many students
• A book can have many tags, and a tag can apply to many books
• An actor can appear in many movies, and a movie has many actors

You cannot represent this with a single foreign key. If you put course_id in students, each student could only ever take one course. If you put student_id in courses, each course could only have one student.

The solution is a junction table (also called a linking table, pivot table, or association table). It sits between the two main tables and holds one row for each relationship:

The enrollments table is the junction. Each row says "student X is enrolled in course Y". The combination of (student_id, course_id) is usually the primary key — it must be unique.

Try modifying the query to answer:

• How many students are in each course? (GROUP BY c.title, COUNT the students)
• Which students are taking more than 2 courses? (HAVING COUNT > 2)
• Which courses does Alice share with Bob? (self-join on enrollments)

The junction table pattern unlocks all of these naturally.

Adding Attributes to the Relationship

One powerful feature of the junction table pattern: you can store data about the relationship itself. In our enrollments table, enrolled_on and grade do not belong to the student or the course. They belong to the act of enrollment.

This is called an enriched junction table, and it is very common in real databases.

Visualizing a Real Join

Here is how the student-enrollment-course join actually links the data at the row level:

Each student row fans out to multiple enrollment rows — this is exactly the one-to-many relationship between students and their enrollments. Then a second join links each enrollment to its course.

A Practical Modeling Workflow for Relationships

When you are modeling a real product, use this order:

1. Identify the core entities first.
2. Ask which side can have many of the other side.
3. Decide whether the relationship itself needs attributes.
4. Add foreign keys and uniqueness constraints that match the rule.
5. Test the design by writing the obvious reporting queries.

That last step is important. A relationship model is only "correct" on paper if the real joins still feel natural in everyday queries.

Choosing the Right Relationship Type

Here is a quick checklist for deciding which pattern to use:

Question	Answer → Pattern
Can one [A] have many [B]? Only?	One-to-Many (FK in B)
Can one [A] have many [B], AND one [B] have many [A]?	Many-to-Many (junction table)
Does the relationship itself carry data (dates, amounts, status)?	Enriched junction table
Is [B] always 1:1 with [A] and mostly accessed separately?	One-to-One (FK as PK in B)

When in doubt, start with a one-to-many. You can always convert it to a many-to-many by introducing a junction table later, and that is a much easier migration than the reverse.

Common Mistakes to Avoid

1. Storing lists of IDs in a single column

-- WRONG: comma-separated IDs in one column
students (id, name, course_ids)
-- where course_ids = '1,3,5'

This makes querying, updating, and enforcing integrity nearly impossible. Use a junction table instead.

2. Forgetting the composite primary key in the junction table

Without PRIMARY KEY (student_id, course_id), nothing prevents a student from being enrolled in the same course twice. Always define the uniqueness constraint.

3. Choosing INNER JOIN when you want all parent rows

If you want all students — including those with no enrollments — you need LEFT JOIN, not INNER JOIN. The inner join silently drops unmatched rows.

Related Articles

• Designing Your First Database Schema for the broader blueprint mindset before you lock in individual relationships.
• Database Normalization Explained for the rules that help you split repeated facts into cleaner related tables.
• Working with JSON in SQL for the tradeoffs that appear when some relationships stay relational and others move into semi-structured fields.

Conclusion

Every database design comes down to these fundamental patterns:

• One-to-Many: The workhorse of relational databases. Foreign key lives on the "many" side.
• Many-to-Many: Handled with a junction table. The junction can carry its own data.

Getting these relationships right up front saves enormous pain later. A schema where students mysteriously cannot enroll in more than one course, or where tags can belong to only one post, is a sign that the wrong relationship type was chosen. With the patterns here, you have everything you need to model most real-world domains accurately.

---

Want to go deeper on schema design? Check out Database Normalization Explained to learn the rules for eliminating redundancy and keeping your tables clean.