CODEWITHSUNDEEP
sqldatabaseenums
ehmicky
ehmicky

Reputation: 2045

When to use an enum or a small table in a relational database?

I have a several small entities in my database that I represent as small table with two columns: id and name. Example of such entities: countries, continent.

Should I create an enum type instead, whenever the name of those entities doesn't matter?

Upvotes: 52

Views: 37880

Answers (4)

Sami Start
Sami Start

Reputation: 2025

Enum is good when:

  • You want to limit the choice of possible values, e.g. days of the week, gender, status of a ticket (opened/closed/archived)
  • You want to reduce storage space (tuples are stored in just 1 byte each)
  • Reduce the number of disc accesses for joins

Enum is bad when:

  • You are not sure what to put as the possible values
  • Possible values are liable to change (this requires admin rights to ALTER tables, which may require you to go offline)

Upvotes: 41

statler
statler

Reputation: 1381

An alternate strategy is to have a synchronizing enum. It is relatively trivial to write code that synchronizes a table to an enum. In this case you can have the benefit of code, but also have the data available in the database table.

An example of when having the data in the table may be important is where you have more than one way to access the database. For example, you may have a separate business reporting app that directly extracts data from the database. If you are running just the enum, then the database will contain only integers with no explanation about what the value means, or the acceptable range of values (for querying).

Code to synch may look something like the following (I am using an ORM here, but the principle should be clear). My code only deals with the additive case - other cases may require edit or even delete of enums to the db. For even more control, you can put an attribute on your enum keys to record different text as the description in the db.

            foreach (ApprovalCategoryEnum category in Enum.GetValues(typeof(ApprovalCategoryEnum)))
            {
                ApprovalCategory asc = existingCategories.FirstOrDefault(x => x.ApprovalCategoryId == (int)category);
                if (asc == null)
                {
                    ApprovalCategory newCategory = new ApprovalCategory(session);
                    newCategory.ApprovalCategoryId = (int)category;
                    newCategory.Description = category.ToString().Replace("_", " ");
                    newCategory.Save();
                }
            }

Upvotes: 3

Lane Wagner
Lane Wagner

Reputation: 75

Enums can be good for performance reasons because strings contain more data than uints. In general as long as you are SURE that you wont be changing the enum mappings it is a good way to go, especially from the coders point of view. It does get a bit hairier when you wan to run raw sql in the database itself though because you need to reference the code to find what all the integers mean.

Upvotes: 3

Barett
Barett

Reputation: 5948

Hate to answer a question with a question, but it depends. How often do you expect the values to change, and how often do you release code?

Enum types will require a code change. A pure database table will be much easier to change. Enum types are more convenient for coding.

For infrequent releases, or if you often have new/deleted/changed values, use a database table. For static sets of values, or if you release code all the time, use an enum.

Upvotes: 51

Related Questions