Relational Modeling: Entities, Attributes, and Relationships
Turning a real-world domain into entities, attributes, and relationships — and the cardinality/optionality questions that decide how those relationships are actually implemented.
What you'll learn
- Identify entities and their attributes from a plain-language domain description
- Classify a relationship's cardinality (one-to-one, one-to-many, many-to-many) and optionality
- Explain why cardinality and optionality decisions directly determine table structure, before any SQL is written
Explanation
Relational modeling starts before any SQL is written, with three plain-language questions about a domain: what are the entities (the distinct "things" worth tracking — a Learner, a Course, an Enrollment), what attributes does each entity have (a Learner has a name and an email; a Course has a title and a duration), and how do entities relate to each other? Getting this modeling step right first is what separates a schema that fits the real domain from one that fights it — a rushed jump straight to CREATE TABLE statements tends to bake in structural mistakes that are expensive to unwind once real data and real application code depend on them.
Every relationship between two entities has a cardinality — how many of one entity can relate to how many of the other. One-to-one (a Learner has exactly one Profile) is genuinely the rarest of the three in practice, since it often means the two entities could simply be one table with more columns. One-to-many (one Course has many Lessons; each Lesson belongs to exactly one Course) is by far the most common shape in real schemas. Many-to-many (a Learner can enroll in many Courses, and a Course has many Learners) cannot be represented with a simple foreign key on either side — it requires a third, connecting table (a junction table, covered in the next lesson) whose rows each represent one specific pairing.
Optionality asks a related but distinct question: is the relationship required or optional on each side? "Every Enrollment must reference exactly one Learner and one Course" (required on both sides) versus "a Learner may or may not have written any Notes yet" (optional) are different constraints, and get expressed differently — a required relationship typically becomes a NOT NULL foreign key; an optional one allows NULL. Getting cardinality and optionality right for every relationship, before writing a single CREATE TABLE, is what this lesson's exercises practice — because these decisions, made explicitly and correctly up front, directly determine which tables need a foreign key, which need a junction table, and which columns must reject a missing value.
Example
Modeling entities/relationships/cardinality as data -- this is genuinely the design work, before any SQL exists.
const domainModel = {
entities: {
Learner: { attributes: ["id", "name", "email"] },
Course: { attributes: ["id", "title", "durationHours"] },
Enrollment: { attributes: ["id", "enrolledAt"] },
},
relationships: [
{
between: ["Learner", "Enrollment"],
cardinality: "one-to-many", // one Learner, many Enrollments
optionality: "Enrollment requires exactly one Learner",
},
{
between: ["Course", "Enrollment"],
cardinality: "one-to-many", // one Course, many Enrollments
optionality: "Enrollment requires exactly one Course",
},
{
between: ["Learner", "Course"],
cardinality: "many-to-many", // via the Enrollment junction table
optionality: "a Learner may have zero Courses; a Course may have zero Learners",
},
],
};
console.log(domainModel.relationships.map(r => r.between.join(" <-> ") + ": " + r.cardinality));Try it yourself
Add a Note entity with a one-to-many relationship FROM Learner (a Learner can have many Notes), then print the updated relationship list.
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Guided exercise
Guided exercise
Write classifyCardinality(description) modeling how you'd classify a plain-language relationship description. Return 'one-to-one' if description contains 'exactly one' on both sides (use the substring 'exactly one' appearing twice), 'many-to-many' if it contains 'many' twice, otherwise 'one-to-many'.
Checks: classifies a mutual one-to-one relationship · classifies a mutual many-to-many relationship · classifies a one-to-many relationship
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Independent exercise
Independent exercise
Write needsJunctionTable(cardinality) that returns true only for 'many-to-many' relationships (the case that cannot be represented with a simple foreign key on either side). Then write requiredForeignKeySide(cardinality) returning 'many' for one-to-many (the foreign key goes on the 'many' side), 'either' for one-to-one, or null for many-to-many (neither side alone can hold it).
Checks: correctly requires a junction table for many-to-many · correctly does not require one for one-to-many · identifies the correct side for a one-to-many foreign key · correctly reports no single valid side for many-to-many
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Common mistakes
- Jumping straight to CREATE TABLE statements without first identifying entities, attributes, and relationship cardinality -- this tends to bake in structural mistakes discovered only after real data and application code already depend on the schema.
- Trying to represent a many-to-many relationship with a foreign key on one of the two original tables -- a single foreign key column can only reference one row on the other side, which cannot express 'many Learners, many Courses' without a junction table.
- Confusing cardinality (how many) with optionality (is it required) -- a relationship can be one-to-many AND optional, or one-to-many AND required; these are two independent questions that both need answering.
Knowledge check
Takeaway
Identify entities, their attributes, and every relationship's cardinality and optionality in plain language before writing any SQL — these decisions directly determine which tables need a foreign key, which need a junction table, and which columns must be required.
Summary
Relational modeling identifies entities, attributes, and relationships. Cardinality (one-to-one, one-to-many, many-to-many) determines table structure; only many-to-many requires a junction table. Optionality (required vs. allowed to be absent) is a separate, equally important question for every relationship.
References
Your notes
Notes save automatically.
Finished this lesson?
Mark it complete to track your progress and schedule a future review.
AI tutor
The optional AI tutor isn't enabled in this deployment. All lessons, exercises, quizzes, and search work fully without it.