Entities, Relationships, Cardinality & Ownership Turn product language into relational structure
Phase 10 - Relational Data Modeling Volume 03 - Database Engineering
↓
↓
Phase 10 decides what the durable data should mean before Phase 11 writes application behavior against it.
Start with business language, not tables A common modeling mistake is to begin by inventing table names. Start instead with statements the business expects to remain true. Example: a workspace has many projects; a project belongs to exactly one workspace; a task belongs to one project; a workspace has many members; a user can belong to many workspaces.
↓
↓
↓
The schema is a durable encoding of business relationships, not a mirror of one screen.
Entity candidates Candidate Why it exists Not just a field because...
User Identity referenced across workspaces It has its own lifecycle and relationships
Workspace Collaboration/security boundary Many projects and memberships depend on it
Project Groups tasks inside a workspace It has ownership, dates and task relationships
Task Work item tracked by the system It has state, priority and relationships
Cardinality Relationship Meaning Relational shape
Workspace 1 -> many Projects Each project belongs to one workspace projects.workspace_id FK
Project 1 -> many Tasks Each task belongs to one project tasks.project_id FK
User many <-> many Workspace Users join many workspaces and vice versa memberships join table
Optionality is part of cardinality "A task may have an assignee" is different from "every task must have an assignee." The first permits a NULL assignee reference. The second should be NOT NULL. Optionality should follow domain rules, not convenience.
A task can be associated with an assignee without the assignee owning the containing project. Ownership affects security and lifecycle decisions. Model this distinction explicitly rather than assuming every foreign key means ownership. A useful review question: If the parent disappears, should the child disappear too? If the answer is unclear, you have not finished modeling lifecycle ownership.
Exercise For each statement below, identify entity candidates, cardinality, optionality, and the likely foreign key or join entity: (1) projects may have an optional due date, (2) users can join many workspaces, (3) each project belongs to one workspace, (4) a task can optionally be assigned to one workspace member.
Authorization decides what an authenticated identity may do to a specific resource. UI hiding is never a substitute for server-side enforcement.
Restate the goal of “Entities Relationships Cardinality and Ownership” as one observable behavior. Write the success signal before touching code.
Trace the boundary map: trusted identity → requested action → resource scope → policy decision → allow/deny. Mark the one boundary this lesson changes.
Inspect the current repository state and record the exact files, route, table, or runtime involved. Avoid creating an abstraction before you can name the existing owner.
Implement the smallest vertical slice. Add one coherent change, run the narrowest verification command, and read the output before continuing.
Connect the slice to TaskFlow’s phase artifact: A complete Prisma/ER model for TaskFlow core entities with keys, relation tables, uniqueness, and important indexes.. Verify behavior from the outside as a user or API client would.
Create one controlled failure that is safe to reproduce. Predict the symptom first, capture evidence, locate the earliest broken boundary, then fix only that cause.
Close the reference and rebuild or explain the core change from memory. Record what you would monitor or test before trusting the same change in production.
const canEdit =
user.role === "ADMIN" ||
(user.role === "MEMBER" && task.ownerId === user.id);
if (!canEdit) throw new ForbiddenException();Do not copy this as a finished answer. Predict what each boundary does, type the smallest useful slice, run it, then compare the result with your prediction.
Trace verified identity, role, resource owner/workspace, and policy inputs separately.
Add server-side resource authorization; UI visibility is only experience, never enforcement.
Without copying the final source, create a second small scenario that applies “Entities Relationships Cardinality and Ownership” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
List TaskFlow nouns and separate entities from attributes and events.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.JSON arrays replacing relational join tables without reason
Predict the symptom before causing the failure. Then locate the earliest boundary where reality diverges from expectation.Why does this responsibility live here, what input does it trust, what output proves success, and what would go wrong if another layer owned it?
If your explanation is vague, repeat the boundary trace before moving on.Close the reference. Reproduce the core idea from memory or explain the exact request/data flow aloud. Then check your diff or notes for accidental shortcuts that bypass validation, authorization, error handling, or verification earned in earlier phases.