Many-to-Many Membership & Composite Uniqueness Model collaboration as a first-class relationship
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.
Why a bare many-to-many is not enough Users and workspaces are many-to-many, but their relationship has meaningful data: role, status, joinedAt, and possibly invitation metadata. That means the relationship deserves its own entity: Membership.
↓
Membership role / status / joinedAt
↓
Membership is not plumbing. It is a domain record with its own behavior and constraints.
Core invariant A user should normally have at most one active membership record per workspace. Encode the structural uniqueness with a composite unique constraint. model Membership { id String @id @default(uuid()) userId String workspaceId String role MembershipRole @default(MEMBER) joinedAt DateTime @default(now())
user User @relation(fields: [userId], references: [id]) workspace Workspace @relation(fields: [workspaceId], references: [id])
@@unique([workspaceId, userId])
@@index([userId])
}Why both an id and composite uniqueness? The surrogate id gives the membership a stable independent identity for APIs/audit events. The composite uniqueness protects the relationship invariant. They solve different problems.
Role belongs on membership A user can be ADMIN in Workspace A and MEMBER in Workspace B. Therefore role cannot be modeled as one global User.role if role is workspace-scoped. This is a foundational enterprise pattern: identity is global, permissions are often contextual. HRIS department membership, tenant membership, project membership and organization membership all use similar relational reasoning.
Assignment should target membership or user? For TaskFlow, a task assignee ultimately represents a user participating in the workspace. Modeling assignee as userId is simpler, but then service logic must verify workspace membership. Modeling assigneeMembershipId makes membership validity structural but couples task assignment to membership row lifecycle. The course will use assigneeId -> User for clarity and enforce membership in the service layer later.
Many to Many Membership and Composite Uniqueness matters because it changes a real boundary in TaskFlow. The goal is to understand the inputs, owner, observable output, and failure modes before adding more abstraction.
Restate the goal of “Many to Many Membership and Composite Uniqueness” as one observable behavior. Write the success signal before touching code.
Trace the boundary map: business noun → invariant → table/relation → constraint → query shape. 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.
model WorkspaceMember {
workspaceId String
userId String
role WorkspaceRole
workspace Workspace @relation(fields: [workspaceId], references: [id])
user User @relation(fields: [userId], references: [id])
@@id([workspaceId, userId])
}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.
Return to the nearest observable boundary: input, network, process log, database, or rendered UI. Compare actual evidence with the predicted path.
Change one variable at a time and reproduce the original failure after the fix; otherwise you have correlation, not diagnosis.
Without copying the final source, create a second small scenario that applies “Many to Many Membership and Composite Uniqueness” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
Model boards/lists/tasks and decide which parent IDs should be required.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.Duplicate membership rows permitted
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.