Hands-On Lab & Phase Exit Practice relational reasoning before persistent CRUD
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.
Level 1 - Follow Recreate the TaskFlow production model on paper or in a diagram. For every foreign key, write the sentence it represents. Example: Project.workspaceId means every project belongs to one existing workspace.
Level 2 - Modify Add an optional Project.dueAt and decide whether it needs an index. Explain your decision from query patterns rather than intuition.
Level 3 - Implement Design a Comment model for tasks. Decide its author relationship, deletion behavior, timestamps, and indexes. Do not copy a generic schema; justify each choice.
Level 4 - Engineer Design workspace invitations without writing application code. Requirements: invite by email, invitation expires, accepting creates membership, repeated active invitations should be controlled, history may need to remain after acceptance. Produce entities, constraints, indexes, and lifecycle policy.
Architecture oral exam Question Expected reasoning
Why is Membership an entity? Many-to-many relationship carries role/status/time metadata
Why role is not User.role? Role is workspace-scoped
Why assigneeId can be nullable? Unassigned task is valid
Why FK does not prove membership? FK proves user exists, not contextual workspace membership
Why composite unique membership? Prevents duplicate user-workspace relationship
Why not index every field? Indexes cost storage and write maintenance
Phase exit checklist • I can distinguish entity, attribute and relationship. • I can identify 1:1, 1:many and many:many relationships. • I can justify NULL vs NOT NULL. • I can identify database invariants vs service-layer business rules. • I can design a join entity when a relationship has data. • I understand composite uniqueness. • I can choose cascade/restrict/set-null intentionally. • I can propose indexes from query patterns. • I understand normalization as one-source-of-truth reasoning. • I can review a schema change as a migration/data operation. • I can explain the complete TaskFlow relational model without reading the code.
Phase 10 is complete when you can explain not only what tables exist, but why each relationship, constraint, optional field, index, and lifecycle decision exists. Phase 11 will then implement persistent CRUD against this model.
Hands On Lab and Phase Exit 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 “Hands On Lab and Phase Exit” 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.
Input -> owner -> transformation -> observable output -> failure evidenceDo 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 “Hands On Lab and Phase Exit” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
Model labels through a join table and explain composite uniqueness.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.Ambiguous cascade-delete behavior
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.