Enum or Lookup Table? Keeping Reference Data in Code and in the Database

Version note: The C# code in this post was tested with .NET 10 and Entity Framework Core (EF Core) 10, and the SQL (Structured Query Language) examples with SQL Server 2025.

Guess First: What Does 3 Mean?

The reporting team got read-only access to the HR (human resources) Portal's database, and the first thing they wrote was a report grouping purchase orders by status:

SELECT StatusId, COUNT(*) AS Orders, SUM(TotalAmount) AS Total
FROM dbo.PurchaseOrders
GROUP BY StatusId;

StatusId Orders Total
-------- ------ -----
1 1 310.0000
2 1 4200.0000
3 2 11050.5000

The application code has an enum called PurchaseOrderStatus. Nobody on the reporting team knows C#. Is 3 "approved" or "rejected"? The same week, the finance unit sends a request: add "Travel" to the "expense type" list on the purchase form, but today, without waiting for the next deploy. These two requests are two faces of the same design decision. By the end of this post one will be an enum and the other a table; guess which is which.

Context and Problem: The Two Natures of Reference Data

Reference data is the small, rarely changing lists used to classify the application's other records: order status, leave type, expense type, currency, country. They all look alike, but they have two different natures.

The first is closed lists. PurchaseOrderStatus values drive the code's behavior: an Approved order is sent to the supplier, a Draft one can be edited. Adding a new status means changing code; it has no meaning without a deploy anyway. The natural home of these lists is the code, that is, an enum.

The second is open lists. Expense types do not change the code's behavior; they exist for grouping in reports and budgets. The finance unit wants to manage this list itself: add to it, rename entries, retire them. The natural home of these lists is the database, that is, a lookup table.

The problem comes from mixing the two natures. When a closed list is kept only as an enum, the database does not know about it: the reporting team cannot decode 3, there is no foreign key, no multilingual label. When an open list is kept as an enum, every new expense type waits for a deploy. And worst of all, because the link between the two is never documented, the enum and the table gradually start saying different things.

Symptoms

Reports and views contain CASE Status WHEN 1 THEN 'Draft' WHEN 2 THEN ... blocks, slightly different in every report.

The business unit opens a ticket in sprint planning for a simple list change.

After a new value is added to an enum, old records show up on screen with the wrong status.

On the Angular side, a copy of the same list sits in a const STATUSES = [...] that is out of sync with the backend enum.

Someone wants to delete a "no longer used" row from a lookup table and either gets a foreign key error or, worse, the orders are deleted too through cascade.

The Naive Solution and Why It Breaks

The naive solution is to write the enum without explicit values and leave it in the database as an int. The first version of the enum, and the second version where months later someone added a value "where it makes sense":

// Version 1: values are not written explicitly
public enum StatusV1 { Draft, PendingApproval, Approved, Rejected }

// Version 2: someone added "Submitted" where it looked logical
public enum StatusV2 { Draft, Submitted, PendingApproval, Approved, Rejected }

The records in the database did not change; only the code did:

int[] stored = [(int)StatusV1.Approved, (int)StatusV1.Rejected];
foreach (int value in stored)
    Console.WriteLine($"stored {value}: v1 reads {(StatusV1)value}, v2 reads {(StatusV2)value}");

stored 2: v1 reads Approved, v2 reads PendingApproval
stored 3: v1 reads Rejected, v2 reads Approved

An approved order now looks "pending approval" and a rejected order looks "approved". The compiler said nothing, and the tests most likely passed, because the test data is created with the new enum every time. What broke is the old rows in the database.

The second common reflex is to store the enum as a string with HasConversion<string>(). The reporting team can now read Approved, and reordering breaks nothing. But this time the name becomes fragile. If someone changes Cancelled to the American spelling Canceled:

public enum RenamedStatus { Draft, Approved, Canceled }

b.Entity<LegacyOrder>().Property(o => o.Status).HasConversion<string>().HasMaxLength(30);

InvalidOperationException: Cannot convert string value 'Cancelled' from the database to any value in the mapped 'RenamedStatus' enum.

Some of the 40,000 orders in the database say Cancelled; the application throws an exception in every query that reads those rows. Storing strings did not remove the problem, it moved it from the number to the name. A rename refactoring is an IDE (Integrated Development Environment) shortcut; a data migration is not.

Options

A. Enum only (stored as int or string)

The fewest moving parts. If values are written explicitly (Draft = 1), the renumbering risk goes away; string storage makes reports readable. But the database does not know the list: there is no foreign key, an invalid value like StatusId = 9 can be written silently, labels and translations stay in the code, and the reporting team has to read the code.

B. Lookup table only

The list lives entirely in the data: Id, Code, multilingual names, sort order and IsActive. A foreign key blocks invalid values, reports become readable with a join, and the business unit manages the list from an admin screen. The cost is that the code cannot behave based on these values. The moment you start writing if (order.ExpenseTypeId == 3), 3 becomes a magic number again and you are back to the sentinels of the third post.

C. Both: an enum, a seeded table and an FK

For closed lists: the code works with the enum, the database has a table that mirrors the enum, and the order table references it with a foreign key. The table's primary key is the enum type itself:

public enum PurchaseOrderStatus
{
    Draft = 1,
    PendingApproval = 2,
    Approved = 3,
    Rejected = 4,
    Cancelled = 5
}

// The database mirror of the enum in the code
public class PurchaseOrderStatusRow
{
    public PurchaseOrderStatus Id { get; set; }
    public string Code { get; set; } = "";
    public string NameTr { get; set; } = "";
    public string NameEn { get; set; } = "";
}

The code keeps writing order.Status == PurchaseOrderStatus.Approved; the reporting team sees Code and NameTr through a join; the FK (foreign key) rejects invalid values. The cost is keeping two sources in sync. The Implementation section of this post automates exactly that.

D. A smart enum class

If the values carry behavior, a closed list of class instances instead of an enum. The "Use enumeration classes instead of enum types" section of Microsoft's .NET microservices guide describes this approach, and ready-made packages such as Ardalis.SmartEnum exist. A simple version without adding a dependency:

// A closed list of values that carries behavior
public sealed record LeaveType(int Id, string Code, bool RequiresDocument, bool IsPaid)
{
    public static readonly LeaveType Annual = new(1, "annual", RequiresDocument: false, IsPaid: true);
    public static readonly LeaveType Sick = new(2, "sick", RequiresDocument: true, IsPaid: true);
    public static readonly LeaveType Unpaid = new(3, "unpaid", RequiresDocument: false, IsPaid: false);

    public static IReadOnlyList<LeaveType> All { get; } = [Annual, Sick, Unpaid];

    public static LeaveType FromId(int id) => All.SingleOrDefault(t => t.Id == id)
        ?? throw new ArgumentOutOfRangeException(nameof(id), $"Unknown leave type {id}.");
}

Instead of if (type == LeaveType.Sick && !hasDocument) you write if (type.RequiresDocument && !hasDocument); adding a new leave type is a single line. The cost is a value converter in EF Core (Id ↔ LeaveType) and losing the compiler checks an enum gives you in switch expressions. Without behavior, there is no need for this much structure.

Implementation: Status in the Code, Expense Type in the Data

In the HR Portal we separate the two lists by their nature. PurchaseOrderStatus is a closed list: Option C. Expense type is an open list: Option B.

// A list that grows without a deploy: no enum in the code
public class ExpenseType
{
    public int Id { get; set; }
    public string Code { get; set; } = "";
    public string NameTr { get; set; } = "";
    public string NameEn { get; set; } = "";
    public int SortOrder { get; set; }
    public bool IsActive { get; set; } = true;
}

public class PurchaseOrder
{
    public int Id { get; set; }
    public PurchaseOrderStatus Status { get; set; }
    public int ExpenseTypeId { get; set; }
    public decimal TotalAmount { get; set; }
}

The labels live in a single place in the code:

static readonly Dictionary<PurchaseOrderStatus, (string Tr, string En)> StatusLabels = new()
{
    [PurchaseOrderStatus.Draft] = ("Taslak", "Draft"),
    [PurchaseOrderStatus.PendingApproval] = ("Onay bekliyor", "Pending approval"),
    [PurchaseOrderStatus.Approved] = ("Onaylandı", "Approved"),
    [PurchaseOrderStatus.Rejected] = ("Reddedildi", "Rejected"),
    [PurchaseOrderStatus.Cancelled] = ("İptal edildi", "Cancelled"),
};

The heart of the configuration is HasData. The rows of the lookup table are generated from the enum; when a value is added to the enum, the next migration contains an InsertData that adds the row. That way the enum and the table change in the same commit.

b.Entity<PurchaseOrderStatusRow>(s =>
{
    s.ToTable("PurchaseOrderStatuses");
    s.Property(x => x.Id).ValueGeneratedNever();
    s.HasIndex(x => x.Code).IsUnique();
    // One row per enum value: if the enum changes, so does the migration
    s.HasData(Enum.GetValues<PurchaseOrderStatus>().Select(v => new PurchaseOrderStatusRow
    {
        Id = v,
        Code = v.ToString(),
        NameTr = StatusLabels[v].Tr,
        NameEn = StatusLabels[v].En
    }));
});

b.Entity<PurchaseOrder>(p =>
{
    p.Property(x => x.TotalAmount).HasPrecision(19, 4);
    p.Property(x => x.Status).HasColumnName("StatusId");
    p.HasOne<PurchaseOrderStatusRow>().WithMany().HasForeignKey(x => x.Status)
        .OnDelete(DeleteBehavior.Restrict);
    p.HasOne<ExpenseType>().WithMany().HasForeignKey(x => x.ExpenseTypeId)
        .OnDelete(DeleteBehavior.Restrict);
});

The two OnDelete(DeleteBehavior.Restrict) lines are not decoration. EF Core sets up cascade delete by default for required relationships; without these lines, the script generated on the first attempt contained this:

CONSTRAINT [FK_PurchaseOrders_PurchaseOrderStatuses_StatusId] FOREIGN KEY ([StatusId]) REFERENCES [PurchaseOrderStatuses] ([Id]) ON DELETE CASCADE

Deleting a status from the lookup table would have deleted every order in that status. Foreign keys pointing at reference data should almost always be NO ACTION. The schema and seed rows generated with Restrict:

CREATE TABLE [PurchaseOrderStatuses] (
    [Id] int NOT NULL,
    [Code] nvarchar(100) NOT NULL,
    [NameTr] nvarchar(100) NOT NULL,
    [NameEn] nvarchar(100) NOT NULL,
    CONSTRAINT [PK_PurchaseOrderStatuses] PRIMARY KEY ([Id])
);
...
INSERT INTO [PurchaseOrderStatuses] ([Id], [Code], [NameEn], [NameTr])
VALUES (1, N'Draft', N'Draft', N'Taslak'),
(2, N'PendingApproval', N'Pending approval', N'Onay bekliyor'),
(3, N'Approved', N'Approved', N'Onaylandı'),
(4, N'Rejected', N'Rejected', N'Reddedildi'),
(5, N'Cancelled', N'Cancelled', N'İptal edildi');
...
    CONSTRAINT [FK_PurchaseOrders_PurchaseOrderStatuses_StatusId] FOREIGN KEY ([StatusId]) REFERENCES [PurchaseOrderStatuses] ([Id]) ON DELETE NO ACTION

The reporting team's query is now readable:

SELECT s.Code, s.NameTr, COUNT(*) AS Orders, SUM(p.TotalAmount) AS Total
FROM dbo.PurchaseOrders p
JOIN dbo.PurchaseOrderStatuses s ON s.Id = p.StatusId
GROUP BY s.Code, s.NameTr;

Code NameTr Orders Total
---- ------ ------ -----
Draft Taslak 1 310.0000
PendingApproval Onay bekliyor 1 4200.0000
Approved Onaylandı 2 11050.5000

Syncing is not one-directional. Someone can add a status to the database by hand, or a migration may have been skipped. A small check that compares the enum with the table at startup catches this in the first minute:

// At startup: do the enum and the table say the same thing?
public static async Task<bool> VerifyAsync(HrPortalContext db, TextWriter log)
{
    var rows = await db.PurchaseOrderStatuses.AsNoTracking().ToDictionaryAsync(r => (int)r.Id, r => r.Code);
    var code = Enum.GetValues<PurchaseOrderStatus>().ToDictionary(v => (int)v, v => v.ToString());

    var problems = code.Where(c => !rows.TryGetValue(c.Key, out var dbCode) || dbCode != c.Value)
        .Select(c => $"enum {c.Key}={c.Value} has no matching row")
        .Concat(rows.Where(r => !code.ContainsKey(r.Key)).Select(r => $"row {r.Key}={r.Value} is unknown to the code"))
        .ToList();

    foreach (string p in problems)
        log.WriteLine($"PurchaseOrderStatus mismatch: {p}");
    log.WriteLine(problems.Count == 0 ? "PurchaseOrderStatus lookup is in sync." : $"{problems.Count} mismatch(es).");
    return problems.Count == 0;
}

Suppose someone added OnHold by hand and deleted Cancelled in the database:

INSERT dbo.PurchaseOrderStatuses (Id, Code, NameTr, NameEn) VALUES (6, N'OnHold', N'Beklemede', N'On hold');
DELETE FROM dbo.PurchaseOrderStatuses WHERE Id = 5;

PurchaseOrderStatus mismatch: enum 5=Cancelled has no matching row
PurchaseOrderStatus mismatch: row 6=OnHold is unknown to the code
2 mismatch(es).

Whether this check is wired into a health check or a fail-fast startup step is the team's call; what matters is that the difference does not stay silent.

For expense types there is nothing in the code; the list is managed through the API (Application Programming Interface). The dropdown in Angular gets its data from /api/lookups/expense-types, and the admin screen calls the add and deactivate endpoints:

app.MapGet("/api/lookups/expense-types", async (string? lang, HrPortalContext db) =>
    await db.ExpenseTypes
        .Where(t => t.IsActive)
        .OrderBy(t => t.SortOrder)
        .Select(t => new { t.Id, t.Code, Name = lang == "tr" ? t.NameTr : t.NameEn })
        .ToListAsync());

app.MapPost("/api/admin/expense-types", async (ExpenseType body, HrPortalContext db) =>
{
    db.ExpenseTypes.Add(body);
    await db.SaveChangesAsync();
    return Results.Created($"/api/admin/expense-types/{body.Id}", new { body.Id, body.Code });
});

// No delete: deactivate, so old orders stay intact
app.MapPost("/api/admin/expense-types/{id:int}/deactivate", async (int id, HrPortalContext db) =>
    await db.ExpenseTypes.Where(t => t.Id == id)
        .ExecuteUpdateAsync(s => s.SetProperty(t => t.IsActive, false)) == 1
        ? Results.NoContent() : Results.NotFound());

The finance unit's request of the day, followed by retiring an expense type:

GET  /api/lookups/expense-types?lang=tr
[{"id":1,"code":"office-supplies","name":"Ofis malzemesi"},{"id":2,"code":"software","name":"Yazılım lisansı"},{"id":3,"code":"training","name":"Eğitim"}]

POST /api/admin/expense-types  {"code":"travel","nameTr":"Seyahat","nameEn":"Travel","sortOrder":40}
{"id":4,"code":"travel"} [201]

POST /api/admin/expense-types/2/deactivate
[204]

GET  /api/lookups/expense-types?lang=en
[{"id":1,"code":"office-supplies","name":"Office supplies"},{"id":3,"code":"training","name":"Training"},{"id":4,"code":"travel","name":"Travel"}]

GET  /api/purchase-orders/2?lang=tr
{"id":2,"status":"Approved","statusName":"Onaylandı","expenseType":"Yazılım lisansı","expenseTypeActive":false}

"Travel" entered the list without a deploy. "Software license" is no longer in the dropdown for new orders, but order number 2, which uses it, still shows its name. Had we tried to delete it instead of deactivating it, the database would have refused thanks to Restrict:

Msg 547, Level 16, State 1, Server b8be943222fa, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_PurchaseOrders_ExpenseTypes_ExpenseTypeId". The conflict occurred in database "HrPortal", table "dbo.PurchaseOrders", column 'ExpenseTypeId'.

Lookup data rarely changes and is requested on every screen. Instead of going to the database on every request, caching it with IMemoryCache or the HybridCache that arrived with .NET 9, and clearing the cache in the admin endpoints, is the natural next step; that mechanism was covered in the ASP.NET Core Web API series post "Caching in ASP.NET Core Web API: IMemoryCache, Redis and Response Caching".

Trade-off Table

When Not to Use It

If the code branches on a value, do not put that list only in a lookup table. The moment you see a condition like ExpenseTypeId == 3, the list has turned into a closed list; it should either be mirrored by an enum (C) or the behavior should move into the table as a column (like RequiresReceipt).

Do not create a lookup table for technical enums that never show up outside. For a background job's internal state or a [Flags] permission mask, a table, a sync check and migrations are pure cost. The bits of a [Flags] enum do not map to a single table row anyway.

Do not write a smart enum for a list without behavior. A smart enum with three properties and zero methods is no different from an enum plus a dictionary, and it brings an extra converter in EF Core.

If multilingual labels are going to go beyond two languages, do not keep adding NameTr/NameEn columns; a separate translation table is the topic of the twelfth post.

Checklist

• Has every list been classified as closed (drives code behavior) or open (managed by the business unit)?
• Are enum values written explicitly (Draft = 1), and is inserting values in between forbidden?
• If an enum is stored as a string, is there a rule or test that prevents renaming its members?
• Are foreign keys to lookup tables NO ACTION (Restrict) rather than cascade?
• Is the table mirroring an enum generated from the same source with HasData, and compared at startup?
• Are retired values marked with IsActive = false instead of being deleted, and do old records keep showing their names?
• Are the lists in Angular fed from the API, or is there a copy in the code?
• Is lookup data cached, and is the cache cleared on admin changes?

Related Patterns and Sources

Primary sources: the EF Core Value Conversions page on Microsoft Learn (HasConversion<string>() and the built-in EnumToStringConverter), the EF Core Data Seeding page (model seeding with HasData), the "Use enumeration classes instead of enum types" section of the .NET Microservices: Architecture for Containerized .NET Applications guide, and the documentation of the Ardalis.SmartEnum package.

From the published series, the C# post "Enums in C#: Underlying Types, [Flags], Parse and switch" covers the language side of enums, and the EF Core posts "Seeding in EF Core: Model Seeding with HasData and Application-Side Seeding" and "Owned Types and Value Converters in EF Core: Addresses, Enums and Strongly Typed Ids" cover the mechanics. For the cache side, "Caching in ASP.NET Core Web API: IMemoryCache, Redis and Response Caching" and "What's New in .NET 9: Built-in OpenAPI, Scalar and HybridCache in ASP.NET Core". Within the series, the order in which statuses may change (transition tables and state machines) is in the ninth post, multilingual data in the twelfth, and settings that vary by tenant in the fourteenth. When the status itself starts carrying behavior, the planned Design Patterns series post "State Pattern: Managing Order States with State Objects Instead of switch" takes over.

A Small Experiment

★☆☆ Add OnHold = 6 to PurchaseOrderStatus and run the application again. What does LookupCheck say? Generate a migration and find the InsertData line.

★★☆ Write a test that catches renamed enum members: the PurchaseOrderStatuses.Code values in the database must match Enum.GetNames<PurchaseOrderStatus>() exactly. Which refactoring does this test block, and which does it allow?

★★★ Add a RequiresReceipt column to expense types and validate against that rule in the purchase order creation endpoint. Then cache the lookup endpoint with HybridCache and clear the cache in the admin endpoints; work out the longest time a retired type can still appear in the dropdown.

This post closes the first block of the series: identity, virtual records, sentinels and reference values. The next block looks at the record's lifecycle, and takes on, this time in full, the question we opened with a small Pattern Template in the first post: doing soft delete right.