Human-Readable Type Tables, Not ENUMs

I was writing the service_types migration last week and hesitated. The older borrowing module in the system relied heavily on an ENUM status column. My initial guess was that building a dedicated lookup table for this new module would be over-engineering. An ENUM seemed faster to write and easier to enforce at the database level. But as I stared at the schema draft, the limitations of that choice became obvious. Human readable type tables offer a flexibility that ENUMs simply cannot match without costly schema alterations.

The Migration Shape

The final schema design for the lookup table prioritized stability and clear relationships. The table includes a unique VARCHAR code, a human-readable label, a BIGINT NULL category_id with an ON DELETE SET NULL foreign key, and an is_active boolean. I specifically chose SET NULL over CASCADE. If a category is deleted, the associated service types must remain intact, merely uncategorized, rather than vanishing entirely. This aligns with standard relational database behavior. As the MySQL documentation explicitly states, for a SET NULL action to succeed, the child columns must not be declared NOT NULL [2]. This small detail prevents accidental data loss when organizational categories are restructured or deprecated.

The Hidden Cost of ENUM

Relying on ENUM columns introduces subtle, compounding technical debt. Under the hood, the database assigns an implicit numeric index to each ENUM value, starting at 1. This often leads to dangerous confusion where number-like string values are accidentally treated as their internal index rather than their literal string representation [1]. A query filtering for the string '2' might inadvertently match the second ENUM value, regardless of what that value actually represents. Furthermore, adding a new service type requires an explicit column-definition change. Changing a column definition touches every row's schema contract. Appending a row to a human-readable type table is a plain INSERT with no schema change at all.

A Two-Layer Guard

Database constraints are powerful, but they should not be the first line of defense for application logic. I implemented a CategoryExists check in the Go service layer to return friendly, actionable errors to the API consumer. The database foreign key constraint then serves as the absolute last line of defense. This two-layer approach is a standard relational practice that prevents orphaned records while ensuring the application can gracefully handle invalid state transitions before they reach the storage engine [3]. The application layer provides user context; the database layer provides absolute referential integrity.

Stable Seeding and the Admin Page

Data consistency across environments is critical for reliable deployments. I structured the seeding process using INSERT ... SELECT statements keyed by the unique VARCHAR code. This guarantees that the primary key IDs remain stable across development, staging, and production environments, preventing foreign key mismatches during automated deployments. If a new environment is spun up, the seed script safely ignores existing rows and only inserts missing codes. On the frontend, the admin page utilizes this structure effectively. It features category filters, active status badges, and permission-gated buttons. The interface only prunes the available choices for the user, while the underlying database schema rigorously validates every mutation.

Sources

1. MySQL 5.7 Manual — The ENUM Type
2. MySQL 5.7 Manual — FOREIGN KEY Constraints
3. PostgreSQL Docs — Constraints