Why is using a common-lookup table to restrict the status of entity wrong?
- by FreshCode
According to Five Simple Database Design Errors You Should Avoid by Anith Sen, using a common-lookup table to store the possible statuses for an entity is a common mistake.
Why is this wrong?
I disagree that it's wrong, citing the example of jobs at a repair service with many possible statuses that generally have a natural flow, eg.:
Booked In
Assigned to Technician
Diagnosing problem
Waiting for Client Confirmation
Repaired & Ready for Pickup
Repaired & Couriered
Irreparable & Ready for Pickup
Quote Rejected
Arguably, some of these statuses can be normalised to tables like Couriered Items, Completed Jobs and Quotes (with Pending/Accepted/Rejected statuses), but that feels like unnecessary schema complication.
Another common example would be order statuses that restrict the status of an order, eg:
Pending
Completed
Shipped
Cancelled
Refunded
The status titles and descriptions are in one place for editing and are easy to scaffold as a drop-down with a foreign key for dynamic data applications. This has worked well for me in the past. If the business rules dictate the creation of a new order status, I can just add it to OrderStatus table, without rebuilding my code.
