Phil Bernie Consulting

I was talking a while back with a colleague about the Enums and Reference tables in databases and how it would be cool if you could somehow bridge the gap between the two in code.

To demonstrate the problem, here’s a simple scenario:
Suppose I have two tables,

Order
-----
OrderId
OrderStatusId (FK)

and

OrderStatus
------------
OrderStatusId (PK)
OrderStatusDescription

Now this is a pretty normal table structure. I can enter in order statuses into my table as

1 – Open
2 – Shipped
3 – Closed

The good thing about this is that I can use a foreign key to enforce referential integrity and I can easily use joins to get the OrderStatusDescription for displaying to the user.

The disadvantage is that there is no guarantee that the PK values of these statuses will always be the same unless I deploy the table data using a script or something. Even then it doesn’t stop someone from manually going in and changing the data, which doesn’t matter so much I gues, but sometimes in code I’d really like to be able to guarantee the safety of something like a select case statement that looks like this:

int orderStatus = MyGetOrderStatusFunction();
switch(i)
{
    case 1:
    case 2:
}

etc.

I don’t like to do this because there’s no strong typing between the primary key values of the order status table and the values I’ve coded for in my switch statement. Really the only thing I can do is perform the switch on the status description string, but even then, there’s no safety. So you’re probably thinking use an Enum already. Well I would, except then you lose the benefits of being able to enforce referential integrity and displaying the description to the users is overly complicated.

What would be nice is if you define a new type of table in SQL called an "EnumTable" which could be used as a table in your SQL, but also referenced as an enum in code. When you define an EnumTable you’d also have to specify key value pairs for the data and that would then form part of the table definition so that every deploy of the database that you do has an identical copy of the table data so that in your code you get the type safety of an Enum whilst having the database flexibility to join on the Enum values.