Must enumeration-table keys be integers?

No. Stable text codes or other suitable keys can be used if their uniqueness and meaning are managed.

Does a foreign key stop users selecting an inactive value?

Not by itself when the referenced row still exists; an additional rule is required.

Should every short list use a table?

No. Native enums or checks may suit some stable lists; metadata and maintenance needs influence the choice.

AI Glossary / Data engineering and architecture / Intermediate

Enumeration Table

Definition

An enumeration table is a table that lists the allowed alternatives for a category, usually with a stable code and a readable label. Other records can reference those codes instead of repeating uncontrolled text. It is often called a lookup or reference table. The exact label is not universal: some products use enumeration table for a specific internal structure, while this entry explains the general database design pattern.

What it means

Consider a support system with several contact channels. A channel table can contain one row each for telephone, email, and web. Ticket records store the relevant channel code. The table provides a central place for definitions and useful metadata, while a foreign key can enforce that stored ticket codes refer to recognized rows. This separates the list of categories from the many records that use them.

How it works

Create a key that uniquely identifies each alternative and add attributes such as its label, description, or display order where needed. Reference that key from related tables and enforce the relationship with database constraints. Design changes carefully: adding a row is not the same as changing application behavior, and marking a row inactive does not automatically prevent new references. Historical interpretation, localization, and retirement rules may require more than a simple two-column table. Decide which rules are enforced by the database and which by application logic.

Three practical examples

Content publishing: articles reference an editorial-status table whose values have shared definitions and a deliberate display order.
Operations reporting: service records reference a cause-code table, allowing a consistent description to appear across reports.
AI annotation: labeled examples reference a label catalog that includes guidance, helping reviewers distinguish categories and maintain an interpretable training dataset.

Think of it this way

Think of a directory that turns extension numbers into department names. Many records can use the same extension while the directory provides the readable meaning. The analogy is limited because a database reference table needs uniqueness and relationship rules, and changing a department's name may have historical implications. A directory also does not automatically prove that a particular call was routed to the correct department.

Worked example

A toy Channel table contains E = email, P = telephone, and W = web. Four Ticket rows reference E, P, E, and W. A join to Channel produces readable labels, and grouping by the code gives two email tickets, one telephone ticket, and one web ticket. An attempted Ticket with code X fails an enforced foreign key because no corresponding Channel row exists. Now mark P inactive. The existing reference is still valid because P remains in Channel; the foreign key does not inspect the active flag. If new tickets must not use inactive channels, an additional rule is needed. Deleting P outright may be blocked by existing references or handled according to configured referential actions, which must be selected deliberately rather than assumed.

Illustrative case study

Fictional example: Willow Services initially lets each office type its own closure reason. The team creates a shared reference table, but discovers that “customer cancelled” and “duplicate request” were previously combined under one local label. It reviews the ambiguous historical records instead of inventing a precise conversion. New records use the clearer categories, and the migration notes preserve uncertainty in old data. This invented case demonstrates vocabulary governance, not a claim that introducing a table automatically fixes every historical classification.

Common misconceptions and mistakes

Do not confuse a reference table with a native database enum type. Both can constrain alternatives, but their change processes and metadata capabilities differ. Do not assume a foreign key enforces every business condition on a referenced row. Avoid relying on row order for display order; store or specify that order explicitly. Also avoid one oversized table mixing unrelated domains unless its keys and constraints reliably prevent, for example, using a country code where a ticket status is required.

Business use

The pattern is useful when categories need descriptions, translations, ownership, or retirement metadata, and when several systems must share definitions. Establish who can change rows and how applications learn about changes. Test reports, caches, integrations, and forms when new alternatives appear. For sensitive historical analysis, consider whether a renamed label changes only presentation or changes meaning; those are different events. A stable code with documented semantics can support consistent reporting, but careless code reuse can undermine that consistency.

Check your understanding

A lookup row's label changes from Phone to Telephone while its code stays P. Must every referencing ticket change?

Answer: Usually the stored references remain P and a join shows the new label. Whether historical reports should show the old wording is a separate requirement that may need versioned reference data or an explicit historical snapshot.

Related terms

Learn next

create a three-row reference table and a small referencing table. Test an unknown code, a retired code, a label change, and an attempted deletion. Record which outcomes are enforced by foreign keys and which need additional rules, then compare that behavior with a native enum.

Sources

Sources: PostgreSQL, Foreign Keys, https://www.postgresql.org/docs/16/tutorial-fk.html ; PostgreSQL, Constraints, https://www.postgresql.org/docs/current/ddl-constraints.html ; PostgreSQL project wiki, Enum, https://wiki.postgresql.org/wiki/Enum . Primary project search excerpts consulted for referential constraints and enum alternatives. IBM OpenPages enumeration-table search results confirm product-specific terminology, but its full page was inaccessible; no specific OpenPages table layout is asserted here.

Previous: Enumeration Encoding Next: Environment