An enum is a database type whose permitted values come from a finite set of named labels. It replaces repeated free-form text with controlled values, improving consistency and reducing redundancy. For example, an ACH exception can use the severity labels error, warning, or info. section 8.4.1
Use an enum when values are limited and unlikely to change frequently, while weighing these design concerns:
- Define the type once, then use it as a column type with NOT NULL or a default where appropriate. section 8.4.1 section 10.2.1
- Choose labels that represent stable categories, such as EIN, SSN, mailing, street, or error. section 10.2.1
- Prefer a separate lookup table with a foreign key when values need frequent changes, database change requests are costly, or portability matters. section 8.4.1 section 10.2.1
- Query the database catalog when an application needs to extract enum labels for a dynamic user-interface list. section 10.2.1
ACH exception severity
In PostgreSQL, define ach_exception_severity with error, warning, and info labels. Store that type in ach_exception_codes alongside each exception code and description, then reference the code from ach_exceptions with a foreign key. Seed standard codes during database startup so dashboards can use consistent, persistent data. section 8.4.1
Listing 8.2 Creating the database ENUM and exception code table
CREATE TYPE ach_exception_severity AS ENUM
➥ ('error', 'warning', 'info'); #1
CREATE TABLE ach_exception_codes (
ach_exception_codes_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
exception_code VARCHAR(3) NOT NULL UNIQUE,
exception_severity ACH_EXCEPTION_SEVERITY NOT NULL, #2
exception_description VARCHAR(255) NOT NULL
);
The listing shows enum declaration, typed-column enforcement, and a related exception-code table in one compact PostgreSQL example. section 8.4.1
