Exposing C# enums as views in database

Question

Say if I have a column in database which can accept any one value of the enum that is defined in my C# code.

There is a usual way to make it be available to the database to store it in a database table and refer it from there.

But sometimes you don't want to store the enum in the database but just want to maintain it in the code, for these cases I have come up with the solution in my project to have a view which will return the values of enums so that we do not have to hard code it in the database, so having something like this in the database

CREATE VIEW ENUM.ValidationFailReasonTypes
AS
SELECT 1 AS [FormLevel], 2 AS [GridLevel]

For enum

public enum ValidationFailReasonTypes
{
    FormLevel = 1,
    GridLevel = 2
}

So just wanted to ask is it a good idea to do like this or is there some better way to handle this case when we don't want to store enum in the database?

The kind of data you want to store in an enum is not the same kind of data you want to store in the database. An enum should be used for things that are extremely unlikely to be changed, while data stored in the database should be used for things that should be easy to change. — Zohar Peled, Apr 18 '19 at 12:31
One of the key benefits to having these things declared in the database is Declarative Referential Integrity, ensuring wrong data cannot end up in the database. But you're not allowed to declare a foreign key where the referenced "table" is in fact a view. — Damien_The_Unbeliever, Apr 18 '19 at 12:36

score 3 · Accepted Answer · answered Apr 18 '19 at 12:47

It all depends.

Old school database folk like me want you to build validity checks into your data model. See this question for how you would do that. You end up with a nice, self-documenting schema, which guarantees that your application can only contain valid entries for ValidationFailReasonTypes.

An alternative view is that the database is just the storage mechanism, and it's perfectly OK for the application to be the guarantor of data validity, especially if it has lots of unit tests. In this case, you use an ENUM in you C#, write unit tests to validate that the entries match your expectations, and accept the fact your database table has an integer column which could contain lots of values, but the application limits it to (1, 2).

I think it's important to pick one approach and stick to it. Your solution - if I understand it correctly - is a bit of both.

In general, I'd use an enum for values that are not going to change without a new release of your application, usually because you're enabling some new functionality.

I'd expect to use a database entry for something that can change in the business domain independently of your application's release. "Grid level" and "form level" feel like features in your application, so I think an enum is okay.

score 0 · Answer 2 · answered Apr 18 '19 at 13:36

The entire reason for an enum is consistency, clarity, and ease of use when coding. I would think you would want that sort of clarity and consistency on the database side as well. If you persist an enum value to the database, you absolutely should have referential integrity on the column and a corresponding definition table. The view saves you nothing in my opinion.

Create a table and add referential integrity to the field even if it only has two rows for all eternity.

Exposing C# enums as views in database

2 Answers2