Tables and Relationships
Learning Objectives
- You can interpret common relationship types in relational schemas.
- You can identify which tables are connected and what join path a query should follow.
In the previous chapter, we looked into the reason why facts are split across tables. The next question is practical: once the facts are split, how do we know which tables belong to one query?
Before writing any join syntax, it helps to study the schema and the relationships.
Start with the Schema
The same schema from the previous chapter stays in use here. For readability, the attributes are omitted from the diagram.
Before writing any query, it helps to pause and ask a simpler question: which table stores the main thing that the question is asking about?
For instance, if the question is about users, then users is a natural starting point. If the question is about course titles, then courses is a natural starting point. This does not yet tell us the whole query path, but it gives us a clearer place to begin reading the relationships.
One-to-Many Relationships
A one-to-many relationship means that one row on one side can connect to many rows on the other side.
In the notation used here, the “one” side is marked with || and the “many” side is marked with o{. In the example below, the courses table is on the “one” side and the course_instances table is on the “many” side.
Examples in our schema include:
- one course can have many course instances,
- one course instance can have many exercises,
- one user can make many submissions.
This pattern usually appears because the “many” side stores a foreign key.
For example, if submissions.user_id points to users.id, then many submission rows may point to the same user row. That is the ordinary-language meaning of the relationship.
It can help to see that idea in a tiny example:
| submission_id | user_id |
|---|---|
| 10 | 4 |
| 11 | 4 |
| 12 | 8 |
These rows can be read in ordinary language like this:
- submission 10 belongs to user 4,
- submission 11 also belongs to user 4,
- submission 12 belongs to user 8.
That is exactly why the relationship is called one-to-many. One user row may be connected to many submission rows, while each submission row still points to one user row.
The useful interpretation is not just “there is a foreign key.” The more practical interpretation is: the foreign key shows which table the query can move to next.
Many-to-Many Relationships
A many-to-many relationship means that both sides may connect to many rows on the other side.
Many-to-many relationships are handled in practice by introducing a linking table that contains one row for each connection. In our schema, the enrollments table is a linking table that connects users and course_instances. Users can have many enrollments, and course instances can have many enrollments, but each enrollment row still points to one user and one course instance.
In this schema:
- one user may participate in many course instances,
- one course instance may contain many users.
That relationship is not stored directly in either main table. Instead, it is stored through the linking table enrollments(user_id, course_instance_id). This is one of the most important ideas in the chapter: in a many-to-many relationship, the relationship itself gets rows of its own.
Each enrollment row says that one user is connected to one course instance. For example:
| user_id | course_instance_id |
|---|---|
| 5 | 12 |
| 8 | 12 |
These rows can be read as:
- user 5 is enrolled in course instance 12,
- user 8 is enrolled in course instance 12.
This is also why many-to-many relationships often feel harder at first. The query cannot move directly from one main table to the other. It has to pass through the linking table because that is where the relationship rows are actually stored.
One-to-One Relationships
One-to-one relationships are less common, so this section is a short background note. If the earlier one-to-many and many-to-many patterns already feel clear, this section can be treated as optional background on a first reading.
One-to-one relationships often appear when optional, private, or rarely used information is separated into a second table. For example, a system might keep a user’s public profile in one table and private settings in another, with one row on each side.
In this course, we do not rely on one-to-one relationships much. The main goal is simply to recognize that not every relationship is one-to-many or many-to-many. It is still useful to mention them here because they remind us that the question is not only which tables are related? but also how many rows can one row connect to on the other side?
Trace the Path Before Writing SQL
Once the relationship shapes are clear, the next step is to trace a path through the schema before writing any JOIN.
Suppose the question is:
- Which users are enrolled in the spring 2026 instance of
CS-A1150?
Before thinking about SQL syntax, read the question in terms of tables:
- start from
courses, - move to
course_instances, - move through
enrollments, - then reach
users.
That gives the path:
courses -> course_instances -> enrollments -> users
Now compare that with a different question:
- Which users submitted the exercise called
SQL Basics?
That suggests a different path:
exercises -> submissions -> users
The key habit is not “start typing JOIN quickly.” The key habit is:
- identify the entities,
- locate the middle table,
- then trace one relationship at a time.
That one pause prevents many later join mistakes. It also makes the later SQL feel less mysterious, because each JOIN clause ends up corresponding to one step that was already named in words.
It is easy to choose tables from the wording of the question but forget to check how they are actually connected. If the path is wrong, the later join conditions are likely to be wrong too.
Question and Path Design
Before writing any join syntax, it is useful to practice one intermediate skill directly: proposing a multi-table question and naming the relationship path that would answer it.
The exercise below asks you to do that in words only. The goal is to show that you can connect the wording of a question to a realistic path through the schema.
Check Your Understanding
- What does one-to-many mean in ordinary language?
- Why does many-to-many usually need a linking table?
- Why is it useful to decide the join path before writing SQL?
Schema Path Puzzle
Before moving on to SQL syntax, it is useful to practice one more step that sits between reading the schema and writing a query: choosing a table order that follows the relationships correctly.
The puzzle below asks you to arrange table names into an order that would help answer a question from the schema. The goal is not to guess quickly, but to trace one relationship at a time and check that each move is supported by the schema.