How I store Enumerations in the Database

One of the things I come across in databases now and then is a collection of single tables with a name like "MessageType". You have a look in them and it turns out to have 6 or so rows with no foreign key relationships. Every single time it turns out to be someone had the idea to 开发者_StackOverflow中文版store an Enumeration (Enum) type in the database. Not a bad idea as it turns out since you can add sort options, soft deletes and the like, but the implementation of a single table for each one is flawed.

"Domain" (or "type") is a fundamental part of relational theory. By definition, it's a set of all possible values of the type in question. (Among other things.)

In some cases, it's impractical or unnecessary to store the set of such values in a table.

• It's not practical to store the set of personal names.
• It's not necessary to store the set of integers.

But in other cases, it makes sense to store the set of all possible values. In a SQL database, you can store that set as part of a CHECK constraint. You can also store that set in a table. The biggest advantage of storing that set in a table is that no code has to be changed when you're surprised to discover a value that you didn't initially know was a member of that set.

So, at a certain theoretical level, you could say that every column in a relational database maps to a foreign key reference to a table containing the set of all possible values. But when it's time to build a database, a lot of those tables might be eliminated as either impractical or unnecessary.

And others might not.

I'm not sure whether this is what you meant, but if you look at this kind of table you won't see any foreign key references. Relevant foreign key references will always be in other tables, referencing this set of all possible values.

An enumeration is effective a set of lookup values though, each with a name and an explicit value - so you could argue that each one does map well to a reference table. If you enforce a foreign key constraint anywhere where these enumerated values are stored, then you also ensure that the data is always within range.

It depends what your scenario is. I've done applications before where I've used "Enum" tables - but more commonly I simply have strongly typed enumerations in my code that then translate into literal strings when they get committed to the database.

e.g.

public enum MessageType
{
    General,
    Comment,
    Feedback,
    Suggestions
}

I don't think there is a right or wrong way.

Storing each enum type in a column of its own is good design. Storing enum values from different types in the same column is bad design, even if you use a second column to disambiguate. Once you've decided to use a separate column for each type, the additional cost of storing that column in a table of its own is minimal.

I would generally have an Id field in that table, and would use that Id field as the target for any needed foreign key references to the enum instance. Such a table can be used for validation and lookup.

For a large and complex database, one with about 500 tables in it, it wouldn't be uncommon for about 150 of those tables to be lookup tables.

Personally I think it is a good idea to store enumerations in database if enum values are used in other types of entities. Furthermore, it is very common to store each enum in different table, otherwise foreign key relationships will not be correct.

For example, if MessageType enum has values:

enum MessageType
{
    Alert = 1, /*each next will be incremented by 1*/
    Error,
    Log
}

Then, corresponding table will have the following structure:

table MessageTypes
{
    ID int,
    Name varchar(50)
}

Here, ID holds the integer values from our MessageType enum (1 => "Alert", 2 => "Error", 3 => "Log"), and is used as a foreign key where needed.

In addition, in code there remains no big need to manipulate the data from MessageTypes table, thus minimizing the data transfer sizes from/to database.

For example, consider this database table:

table Messages
{
    ID int,
    MessageTypeID int /*this is foreign key referring to Messages.ID*/,
    Text varchar(100)
}

In code we probably will have the corresponding entity class like this:

class Message
{
    int ID { get; set; }
    int MessageTypeID { get; set; }
    string Text { get; set; }
}

So, when we retrieve the Message entity, we don't need to retrieve the corresponding row from MessageTypes table, neither we need to have a separate entity like MessageType. Instead, we can directly use already known enum values for checking the message type:

var msg = LoadMessage(id: 101);
if (msg.MessageTypeID == (int)MessageType.Alert)
{
    Console.WriteLine("Alert: {0}", msg.Text);
}

I hope this makes sense.