Enums, Lookup Tables & State Modeling Choose the right representation for finite business states
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.
Not every string should be free text Values such as task status and priority usually come from a constrained set. Free-text values create invalid states such as "INPROGRESS", "in_progress", and "Doing" describing the same concept.
Three common options Approach Good for Cost
Database/Prisma enum Small stable finite set Schema migration to change values
Lookup table Admin-configurable or metadata-rich set More joins and lifecycle complexity
TaskFlow choice enum TaskStatus { TODO IN_PROGRESS DONE CANCELED }
enum TaskPriority { LOW MEDIUM HIGH URGENT }
enum MembershipRole { OWNER ADMIN MEMBER }
Why enums here? These values are small, have clear semantics, and are expected to change infrequently. They are part of the application contract and are good candidates for enums.
When a lookup table would be better If each status needs configurable color, ordering, SLA behavior, translations, or customer-defined values, it has become data rather than a fixed code-level state. A lookup table may then be more appropriate. Do not use enums for values likely to be customized per tenant. Schema migrations are a poor substitute for normal application data management.
State transitions are not automatically database constraints An enum can guarantee that status is one of the allowed values, but it does not guarantee valid transitions such as TODO -> DONE. Transition permissions and workflow rules belong in the service layer unless a database trigger is intentionally part of the architecture.
Enums Lookup Tables and State Modeling 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 “Enums Lookup Tables and State Modeling” as one observable behavior. Write the success signal before touching code.
Trace the boundary map: value → owner → lifetime → source of truth → consumer. 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.
API task rows -> TanStack Query
Search/filter URL -> URL/search params
Create form fields -> React Hook Form
Dialog open/closed -> local UI state
Authenticated user -> auth/session stateDo 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 “Enums Lookup Tables and State Modeling” 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.Using nullable foreign keys where the domain requires ownership
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.