R – e-shop implementation: Status for Orders

What Anith Sen is advising against is having a single lookup table for all lookup codes. That is the significance of the category column in his example. Having a separate table for each category is definitely the way to go.

This is because:

1. we can use the look-up tables to restrict values through enforce foreign keys
2. it makes it easier for the database to optimize queries which join data tables with look-up tables
3. it scales better: one large lookup category can really skew performance

In your examples JobStatus and OrderStatus are separate categories. applicable to separate entities. That is why they need different look-up tables. There is not even a problem with sharing the same code table across several different data tables. Where it becomes problematic is when we have separate data tables (entities) for which some statuses are not appropriate: that is the time to split the codes out into separate look-up tables.

edit

I see you have edited your post to cite all of Anith's points. I think the most important point one is the first one, regarding constraints. If you want to restrict the ORDERS.STATUS column to have values from the OrderStatus category then you have to have a separate table to enforce a foreign key. Your alternatives are:

• include a CodeCategory column on the ORDERS table and enforce a compound foreign key against the common CODES table, which now needs a unique key of (Category,Code).
• duplicate the OrderStatus values in a check constraint
• don't enforce the values in the database, and rely on the application's drop-down list to restrict the values.

All of those options suck, from the database perspective.