Don't model your enums in the database

George Bidilica
George Bidilica · · 6 min read

EAV (entity-attribute-value) stores attributes as rows in a child table instead of columns on the entity. Most teams don’t sit down and design an EAV schema. They drift into one. Someone wants an “employment type” filter on the listings page. Then “work mode”. Then “seniority”. Then “visa sponsorship”. Adding a column per filter feels rigid, an enum in code feels worse (a deploy for every new option), and a generic attribute table with an attribute_option table and a junction looks like the answer. Three tables, full referential integrity, and an admin can add new attributes from a back-office tool. No migration. No code review.

Two flavours exist in the wild. Pure EAV stores the value as a free-form string and has fallen out of fashion. The variant that actually ships pulls each attribute’s values from a curated list, with types and validation. Call it controlled EAV: no typos, admins pick from a list, and renames happen in one place. We’ll just call it EAV for the rest of this post.

Two domains reach for it for opposite reasons. An ecommerce catalogue spans televisions, couches, and motor oil, and next quarter the buyer asks for medical equipment. No two product types share a schema, and the team adding them isn’t shipping code. A job board looks similar from one angle (a curated lookup table per attribute, a junction per listing), but the code secretly branches on half the values. Contract triggers a day-rate field. Confidential listings hide the company name. Hybrid asks for minimum days onsite. Same three tables, two read patterns hiding in one schema.

For options the code stores and renders but never branches on, EAV is the right call. For options the code branches on (in the listing logic, the matching service, or form validation), the FK skips the schema change but lands the same work in code in a downgraded form: row-ID references and string identifiers instead of grep-able enum constants.

Where the shape fits

The ecommerce catalogue is the case EAV was designed for. A TV has screen size, panel type, refresh rate, HDR support. A couch has fabric, frame, seat depth. A fishing rod has length, action, line weight. None of these share a schema, and next quarter the buyer asks for medical equipment or musical instruments. The code has never seen the attributes and never will, because the team adding new product types isn’t shipping code.

The code doesn’t branch on what the values mean. It doesn’t have if (panelType === 'OLED'). It renders the value, offers it as a search facet, and counts occurrences for analytics. The catalogue team picks the list of values, the code treats it opaquely. Faceted search filters by FK without knowing what the FK means. That’s the shape EAV is built for.

Multi-tenant SaaS pushes the same shape further: CRMs, CMSes, no-code builders where the schema is the product and the values were invented outside the codebase entirely.

A job board is the mixed case, and the more common trap. The attributes look the same shape as the ecommerce catalogue: lookup tables for employment type, work mode, seniority, tech stack, benefits, visa sponsorship. Same three tables. But the code treats them very differently. Tech stack is renders-and-counts (EAV is right for it). Employment type triggers form fields: Contract needs a day rate, Permanent doesn’t. Work mode triggers form fields too: Hybrid asks for minimum days onsite. Confidential listings hide the company name from public search results. The job board’s lookup tables hold both kinds of dictionary at once, and the code’s behaviour depends on telling them apart.

Two dictionaries

Every schema holds two kinds of dictionary, and they belong in different places.

A code dictionary is a closed set the code reasons about. The matching service treats CONTRACT specially because contract jobs need a day rate on the listing. The listing logic treats CONFIDENTIAL specially because confidential listings hide the company name from public results. Form validation treats HYBRID specially because hybrid roles need a minimum-days-onsite value. The values drive behaviour, and behaviour-driving values belong in code, in a typed enum where the compiler can find every reader.

A data dictionary is an open set defined by users, tenants, or category teams. A job board’s tech-stack list is one: hundreds of options the matcher treats opaquely. So is its list of benefits. The code stores the values, renders them, filters by ID, but never branches on what they mean. New rows land without a code change.

Teams reach for the attribute / attribute_option / junction pattern for both, and that’s the mistake. The “employment type” filter on a job listings page looks like a data dictionary (an admin can add a row) but is a code dictionary (the moment the row exists, the listing logic needs to recognise it, form validation needs to know whether it’s Contract, and the matching service needs to weight it for candidate fit). The FK gave the team referential integrity. It didn’t give them flexibility.

The lie

The pitch is that EAV decouples schema from code: you can add a new attribute or option without shipping. In practice, almost no option in a real system is a pure tag. Options carry behaviour. Contract jobs require a day rate. Confidential listings hide the company name from public search results. Apprenticeships need a training provider and a start date. The moment an option means something to the code, it has to branch on which option a thing has, which means it has to refer to a specific row in your options table.

You end up writing one of these:

if (job.employmentType.id === 12) {        // CONTRACT
  form.addField('dayRate');
}

if (job.workMode.identifier === 'HYBRID') {
  form.addField('minDaysOnsite');
}

class EmploymentType {
  static CONTRACT = 12;
  static PERMANENT = 10;
}

Pick whichever you like, they’re all the same shape. Code has hardcoded a reference to a row in a table. The schema-less flexibility was a marketing slide. You still have a schema, it just lives in the DB instead of your repo, and nothing keeps the two in sync.

What that costs you

The marketing slide says “just insert a row”. That only works for options with zero behavioural effect. The moment an option does something, you also need a code change, and the migration-free claim collapses to “we saved you a migration on the rare options that don’t do anything”.

Two failure modes do most of the damage in practice. The first is renames. Someone in admin renames the “Confidential” option to “Anonymous” because the recruiting team thought “Anonymous” would be clearer to job seekers. Your if (identifier === 'CONFIDENTIAL') check in the listing logic silently returns false in production. No compile error, no migration warning, no failing test unless someone happened to write one. You find out from an employer complaining that their company name appeared in a public search where it shouldn’t have. Teams learn to lock the identifier column separately from the display name, but the lock is convention, not constraint. The next admin still has to remember which column drives the code.

The second is deploy ordering. A code change that branches on a new employment type (say “Internship”, which needs a training-provider field) needs that option to exist in every environment first. A seed that removes an option needs the code to stop referring to it first. There’s now an implicit deploy order, but nothing enforces it. The drift shows up as a staging env where the form crashes on submit because Internship exists in code but not the DB, or a prod env where Internship exists in the DB but no form renders training-provider yet, so the listing publishes without one. Both are silent in CI. These costs compound when teams paper over them with infrastructure, and that infrastructure has a recognisable shape.

A few smaller costs round it out:

  • Re-seeding shifts the IDs. static CONTRACT = 12 in production is now pointing at “Permanent” in staging.
  • Behaviour splays across two systems. To know what the app does when a job is Contract, grep the codebase and query the DB. You can’t grep a row.
  • Refactors get harder. Renaming “Employment Type” to “Engagement Type” in code is find-and-replace. In the DB it’s a migration. In both, kept in sync across history, it’s a project.

The metadata registry

The anti-pattern that gives away a misclassified lookup table is a central service that knows everything about every lookup. It reads attribute and attribute_option at startup, caches the identifiers, types, and validation rules, and exposes them to every component that needs to know what’s queryable, what’s a facet, what should be a number, what’s required. The service is the schema the team escaped from, rebuilt in code without the database’s help. Cache invalidation, validation, all now code problems. The columns the team didn’t want to migrate are still there, they just live in an in-memory cache instead.

If every attribute were user-owned and behaviour-blind, no service would need the identifiers in advance. The service exists because some of them aren’t.

If you’ll grep for it, model it

For anything code branches on, model it in code. A typed enum, a class per option, a column on the entity table. Yes, this means migrations. Migrations are the feature, not the bug. They’re how a schema change becomes visible in code review, runnable in CI, and replayable in every environment. The grep-ability of EmploymentType.CONTRACT is worth 10 migrations.

Before adding a value to a lookup table, four questions decide it. Does the code branch on this value? Will the search facets care which one it is? Will the matching service treat any of them specially? Will renaming the identifier require a code change? If yes to any, it’s a code dictionary. Put it in code, somewhere the compiler can find every reader. The FK in the database doesn’t change what it is.

The mistake isn’t using EAV. It’s using it past the point where the options stopped being pure data and started being part of the code’s vocabulary. Three tables and a junction feel cheaper than a thousand-line enum file on day one. They are, until month 6. By then the team is maintaining a service whose job is to remember what the enum file would have held, and the migrations are happening across search, matching, forms, and exports, in 4 PRs instead of one.

Once an option has a name in your code, it belongs in your code.