Relationships, Cardinality, and Optionality
Learning Objectives
- You can describe a relationship using cardinality and optionality in ordinary language.
- You can recognize when a relationship itself has attributes that deserve to be stored.
- You can connect modeling decisions made now to the join paths you have already practiced.
Once the main entities have been identified, the next question is how are they connected — what relationships exist between them, and what do those relationships look like?
Relationships Express Structure
A relationship says how one kind of entity is connected to another.
In the course-platform domain, this includes ideas such as:
- a course contains course instances,
- a course instance contains exercises,
- a user submits work for an exercise,
- a user enrolls in a course instance.
In the recipe app, the relationships were:
- a user owns many recipes,
- a recipe uses many ingredients, and one ingredient appears in many recipes,
- a user favorites many recipes.
In the library case, the relationships were:
- a book has many copies,
- a copy has many loans over time,
- a loan belongs to one member.
Each of these is a connection worth naming because later queries will use it.
Cardinality
We briefly discussed the relationship types between tables in the chapter Tables and Relationships of the last part. Now we can give those types more precise meaning.
Cardinality describes how many entities on one side may connect to entities on the other side.
The most common cases are:
- one-to-one: at most one row on each side may connect to one row on the other,
- one-to-many: one row on one side may connect to many rows on the other side,
- many-to-many: rows on each side may connect to many rows on the other side.
In the course-platform example:
- one course can connect to many course instances (one-to-many),
- one course instance can connect to many exercises (one-to-many),
- one user can connect to many submissions (one-to-many),
- many users can connect to many course instances through enrollment (many-to-many).
Reading a relationship in plain sentences — “one course has many instances, and each instance belongs to one course” — usually makes the cardinality obvious.
Optionality
Optionality asks whether the relationship is required.
Examples:
- must every exercise belong to a course instance?
- can a course instance exist before any student has enrolled?
- can a user exist before making any submission?
These questions matter because they later influence constraints and foreign-key placement. An exercise that must belong to a course instance will later have a NOT NULL foreign key. A course instance that can exist without enrollments does not require any enrollment row to exist first.
It is useful to describe both cardinality and optionality together:
- one course must have at least one instance — or may a course exist before any instance has been scheduled?
- an exercise must belong to exactly one course instance,
- a user may make zero or more submissions,
- a user may enroll in zero or more course instances.
Those small words — must, may, at least one, at most one — carry a lot of design information.
Optionality influences the application behavior. Consider a course instance that may exist before any exercise has been created. The application page for that course instance must handle the “no exercises yet” case. A designer who noticed the optionality during modeling is likely to add that handling, while a designer who did not notice may be surprised when the page breaks on a fresh course instance.
Many relationships are required on one side and optional on the other. An exercise must belong to a course instance, but a course instance may exist before any exercise has been created. Recognizing this asymmetry early usually prevents schema confusion later.
Relationship Attributes
Sometimes the relationship itself has information worth storing.
In the recipe app, the connection between a recipe and an ingredient carries a quantity. The quantity is not a property of the recipe on its own (the same recipe uses different quantities of different ingredients), and it is not a property of the ingredient on its own (the same ingredient appears in different quantities across recipes). It belongs to the specific pairing of one recipe and one ingredient.
In the library case, the connection between a copy and a member carries a borrow date and possibly a return date. Those dates do not belong only to the member, and they do not belong only to the copy. They belong to the specific loan event that connects them.
In the course-platform case, an enrollment might carry a joined_at timestamp. The same pattern applies: the timestamp is not a property of the user, and it is not a property of the course instance. It is a property of the enrollment itself.
This is a useful modeling clue. If a candidate attribute makes sense only when both sides of a relationship are known, then the attribute belongs to the relationship, not to either side.
Many-to-Many Relationships Are Special
A many-to-many relationship is worth special attention because it almost always ends up as its own structure in the later schema.
In the course-platform case, enrollment is many-to-many: a user can enroll in many instances, and an instance can have many users. Neither side can store the connection on its own. The users table cannot store a list of instances, and the course_instances table cannot store a list of users.
The usual solution is to pull the relationship out into a structure of its own, where each row represents one specific pairing. In the previous part, when working with joins, you already used such structures through join paths like users -> enrollments -> course_instances. The reason that path made sense is that the relationship itself became visible in the model rather than being hidden inside one column.
The next part turns this conceptual decision into a real linking table. For now, the important point is to recognize many-to-many when it appears and to expect that it will need its own structure later.
Check Your Understanding
- What does cardinality describe?
- What does optionality add to the description of a relationship?
- Why is it useful to ask whether an attribute belongs to a relationship rather than to one of the entities on its sides?
AI Study Aid
Create a chapter diagram
We're looking into to what extent AI-generated diagrams could help with studying.
Use this study aid to generate an AI-generated visual summary of the material you just studied.
Each diagram style emphasizes the content in a different way, so you can choose the focus
that feels most useful.
Using the diagram generator is voluntary and does not affect your course progress. We encourage
you to try it out and see if it helps, but it's totally up to you! Your answers help us understand
how to make better study aids in the future.
Selected style: choose a diagram style
Design Exercise
Use the following exercise to turn the chapter’s relationship ideas into modeling statements. For each relationship, state both cardinality and optionality in plain language, and keep the focus on what the domain allows or requires.