AP
← Field notes

What I Learned About Database Design from Modelling Statuses

Last revised

When I first started designing status fields, I wanted everything to be centralised.

Instead of storing values such as pending, processing, or completed directly on each record, I created a shared status table:

CREATE TABLE status_master (
  id SMALLINT PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  type TEXT NOT NULL
);

Other tables referenced it through a foreign key:

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  status_id SMALLINT REFERENCES status_master(id)
);

At first, this looked like good database design. It gave every status a stable ID, a unique code, a display name, a description, and a category.

It was normalised, reusable, and centrally managed.

After using the pattern across a real application, however, I began to see its cost.

When a Status Becomes an Accidental Entity

A shared status table works well when statuses are genuine business entities. The problem is using it for every state in the system.

In this codebase, the central status_master table grew to cover unrelated workflows:

  • Logistics
  • Production
  • Pick and pack
  • Finance
  • Procurement
  • Claims
  • General requests

Statuses such as DELIVERED, PACKED, APPROVED, PAYMENT_COLLECTED, and WIP_COMPLETED all lived in the same namespace.

More than 20 foreign keys eventually referenced the table.

That centralisation introduced several forms of coupling.

A feature could not create a record without first resolving a status code to an ID:

const status = await db.query.statusMaster.findFirst({
  where: (statusMaster, { eq }) =>
    eq(statusMaster.code, "PENDING_APPROVAL"),
});

if (!status) {
  throw new Error("Status PENDING_APPROVAL not found");
}

Queries also needed a join to expose a meaningful value:

SELECT
  orders.id,
  status_master.code,
  status_master.name
FROM orders
JOIN status_master
  ON status_master.id = orders.status_id;

The join itself was not the problem. PostgreSQL is very good at joining an indexed foreign key to a small lookup table.

The real cost was conceptual and operational:

  • Seed data became part of application correctness.
  • A missing status row could break record creation.
  • Queries and ORM relations became more verbose.
  • Migrations had to coordinate code, foreign keys, and reference data.
  • Looking at status_id = 31 did not reveal the record's state.
  • One global table coupled otherwise unrelated workflows.
  • Status codes existed in both TypeScript and database rows, creating two sources that had to remain aligned.

The design was normalised, but it was more complicated than many of the workflows required.

The Repository Ended Up Showing Three Valid Patterns

The useful lesson was not that one representation was always correct. It was that different kinds of statuses needed different representations.

This repository now contains all three common patterns:

  1. Foreign keys into status_master
  2. PostgreSQL enums for controlled internal workflows
  3. Text columns for external or flexible values

The orders table demonstrates this transition particularly clearly. It contains both the older lookup-based field and a newer direct status field:

statusId: integer("status_id").references(() => statusMaster.id),

statusNew: orderStatusV2Enum("status_new")
  .default("PENDING")
  .notNull(),

The schema itself records the evolution of the design.

A Status Does Not Always Need an ID

Many statuses are not independently managed data. They are simply the current state of a record.

An order can be:

PENDING
PREPARING
PREPARED
IN_TRANSIT
DELIVERED
COMPLETED
CANCELLED

If those states are defined by the application and have no independent lifecycle, storing the value directly makes the record easier to understand:

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  status TEXT NOT NULL
);

A row now explains itself:

id: 1024
status: COMPLETED

There is no lookup required to discover what the status means.

Strictly speaking, this is not necessarily denormalisation. A short status code can be an atomic value in the relation. The important question is not whether the value repeats, but whether it represents an entity that should exist independently.

Repeating COMPLETED across many rows is usually acceptable. An integer foreign key may use less storage at a large scale, but storage savings alone rarely justify a more complicated domain model.

Internal Statuses and External Statuses Are Different

The repository also stores Shopify's fulfillment_status as text:

fulfillmentStatus: text("fulfillment_status"),

That makes sense because it is an external value. The application may need to preserve what Shopify sent, including values introduced outside this codebase.

The internal operational status has different requirements:

statusNew: orderStatusV2Enum("status_new")
  .default("PENDING")
  .notNull(),

This distinction is more useful than applying one status pattern everywhere:

  • External statuses often need flexible text storage.
  • Internal workflow states benefit from stronger validation.
  • User-configurable statuses may deserve their own table.

The Trade-Offs of PostgreSQL Enums

PostgreSQL enums provide strong database-level validation:

CREATE TYPE order_status AS ENUM (
  'PENDING',
  'PREPARING',
  'COMPLETED',
  'CANCELLED'
);

They prevent invalid values regardless of whether a write comes from the application, a script, a migration, or an administrative query.

They also integrate well with Drizzle and TypeScript:

export const orderStatusEnum = pgEnum("order_status", [
  "PENDING",
  "PREPARING",
  "COMPLETED",
  "CANCELLED",
]);

export type OrderStatus =
  (typeof orderStatusEnum.enumValues)[number];

But this safety creates schema coupling.

Adding and renaming enum values is supported by PostgreSQL, although deployment ordering still matters. Removing a value is more involved because existing rows and dependent objects must be handled first.

Enums are therefore a good fit when:

  • The state machine is controlled by the application.
  • Invalid database values would be dangerous.
  • Values are expected to grow but rarely disappear.
  • Schema migrations are already part of the deployment process.

They are less attractive when values change frequently or are controlled by another system.

Text Plus TypeScript Is the Most Flexible Option

For flexible values, a text column can be paired with an application-level definition:

export const orderStatuses = [
  "PENDING",
  "PREPARING",
  "COMPLETED",
  "CANCELLED",
] as const;

export type OrderStatus = (typeof orderStatuses)[number];

Zod can provide runtime validation:

import { z } from "zod";

export const orderStatusSchema = z.enum([
  "PENDING",
  "PREPARING",
  "COMPLETED",
  "CANCELLED",
]);

export type OrderStatus = z.infer<typeof orderStatusSchema>;

This provides strong guarantees inside the TypeScript application, but it does not protect the database from other writers.

A script, manual query, or separate service could still insert an invalid value. If the database has multiple write paths, application validation alone may not be enough.

A check constraint offers a middle ground:

ALTER TABLE orders
ADD CONSTRAINT orders_status_check
CHECK (
  status IN (
    'PENDING',
    'PREPARING',
    'COMPLETED',
    'CANCELLED'
  )
);

A check constraint still requires a migration when values change, but it is generally easier to replace or remove than a PostgreSQL enum type.

When a Status Table Is the Right Design

A status table is appropriate when the status has data or behaviour of its own.

For example:

  • Administrators can create custom statuses.
  • Each tenant has a different workflow.
  • Statuses have configurable colours, icons, or ordering.
  • Statuses contain transition rules.
  • Statuses affect permissions.
  • Labels require localisation.
  • Statuses can be enabled or disabled dynamically.
  • Configuration must change without deploying the application.

In those cases, a status is no longer merely a value. It is an entity that users or the system need to manage independently.

A dedicated table may then be the cleanest representation:

CREATE TABLE workflow_statuses (
  id BIGINT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  code TEXT NOT NULL,
  name TEXT NOT NULL,
  colour TEXT,
  sort_order INTEGER NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  UNIQUE (tenant_id, code)
);

Notice that this table belongs to a specific workflow or tenant. It is not necessarily one global table for every status in the application.

My Current Decision Framework

Before choosing a representation, I ask:

  1. Is the value controlled by our application or an external system?
  2. Can users or administrators configure it?
  3. Does it have meaningful attributes beyond its code?
  4. Does it need to exist independently of the records using it?
  5. How often will values be added, renamed, or removed?
  6. Are there writers that bypass the TypeScript application?
  7. Would one shared namespace couple unrelated workflows?

My default choices are now:

Situation Representation
External or loosely controlled value TEXT
Small internal state machine Enum or TEXT with a check constraint
Type safety needed only in one application TEXT plus TypeScript and Zod
User-configurable workflow Dedicated status table
Tenant-specific statuses with metadata Tenant-scoped status table
Unrelated workflows Separate domains, not one global status table

Final Thoughts

The original status_master design was not inherently wrong. It simply treated every status as the same kind of thing.

The more useful lesson is that "status" is only a column name. One status may be external data, another may be an internal state machine, and another may be a configurable business entity. Those cases should not automatically share the same schema.

Good database design is not about maximising normalisation or minimising joins. It is about modelling the domain accurately while keeping the system understandable.

Sometimes that means a foreign key.

Sometimes it means an enum.

And sometimes the clearest design really is:

status TEXT NOT NULL
← Back to all notes