In my experience, the question around putting enums and their descriptions into code or the database, really depends on the complexity and target audience of the app you're writing.
To be more precise, I tend to store descriptions of enum values in the database when there is a chance that I'll be localizing the database and need to share that localization with reporting solutions or 3rd party systems that won't accept localization through some other more traditional method.
Having said that, I wouldn't suggest extrapolating enums and their descriptions into an entity-attribute-value system. Typically, I might create an enum table for each data or fact table in my database. So, if I have a table for storing Invoices, and that table has a column for InvoiceStatus, I might have a table called InvoicesStatusDescriptions with a couple of rows that describe the various invoice statii. Then, if I want to quickly localize the app, I can simply change the description value for each enum value.
An example may help:
CREATE TABLE dbo.Invoices
(
InvoiceID int NOT NULL
IDENTITY(1,1)
CONSTRAINT Invoices_pk
PRIMARY KEY
CLUSTERED
, InvoiceStatusID tinyint NOT NULL
CONSTRAINT Invoices_status_fk
FOREIGN KEY
REFERENCES dbo.InvoiceStatusDescription(InvoiceStatusID)
);
The InvoiceStatusID column references the following table:
CREATE TABLE dbo.InvoiceStatusDescription
(
InvoiceStatusID tinyint NOT NULL
CONSTRAINT InvoiceStatusDescription_fk
PRIMARY KEY
CLUSTERED
, InvoiceStatusDescription nvarchar(30) NOT NULL
);
Some sample data:
INSERT INTO dbo.InvoiceStatusDescription (InvoiceStatusID, InvoiceStatusDescription)
VALUES (1, N'Started')
, (2, N'Payment Requested')
, (3, N'Payment Received')
, (4, N'Closed');
INSERT INTO dbo.Invoices (InvoiceStatusID)
VALUES (1)
, (3);
Then you can provide a query like this to get localized versions of the status of each invoice:
SELECT i.InvoiceID
, id.InvoiceStatusDescription
FROM dbo.Invoices i
INNER JOIN dbo.InvoiceStatusDescription id ON i.InvoiceStatusID = id.InvoiceStatusID
With the sample data, output looks like this:
| InvoiceID |
InvoiceStatusDescription |
| 1 |
Started |
| 2 |
Payment Received |
You can even add a column to the InvoiceStatusDescription table indicating the locale, but in my experience it would be easier to simply replace the description values in each enum table when the user wants to switch to a different locale.
Having said all that, your code is still going to need to understand the meaning of the enum values, so you'll still likely create discrete enums in your code. Keeping the two systems in sync can be a challenging problem space in large systems.
