Is it worth normalising enumeration values into a new table?

Question

When designing software with databases is it worth normalising enumerations into separate tables?

e.g. if I have a class:

public class Alert
{
    public int ID
    public System.DayOfWeek AlertDay;
    public string Message;
}

Should my corresponding Alert table simply be something like:

CREATE TABLE Alert
(
    ID INT IDENTITY,
    AlertDay INT,
    Message VARCHAR(50)
)

Or should I normalise the days of the week to a separate table and add a FK relationship to my Alert table?

Is there a rule of thumb for this kind of design decision?

Answer 1

I don't. But try to keep the string Message as short as possible, and use an index on it on the denormalized table.

Have a look at Enum Fields VS Varchar VS Int + Joined table: What is Faster? from MySQL Performance Blog.

Answer 2

If it's something so constant as the information about the days of the week, I would just define a enum in the code and store the integer in the database.

Answer 3

Not usually. The days of the week aren't likely to change, and you don't want to have to perform an unnecessary join.

On the other hand, you do want to make sure that the source of your "days of the week" is consistent and not going to change (e.g. localization, capitalization, abbreviation, etc).

Answer 4

Will the data be accessed directly from the database without going through your objects? If so then I would recommend a lookup table or anyone writing a report by day of week may become a bit annoyed when they have to write a case statement to display the text values of the day of week or when they make the mistake of converting the int to day of week using a system function and find out that the startOfWeek setting on their server is different than what you had hard coded into your object.