Using 1 digit codes VS descriptive strings for ENUMs in a database?

In my user_accounts table, I have a field called source which is an ENUM. It indicates how the user was referred to the website. Possible values are via Facebook, via Email, or via regular website signup.

There are 3 possible options for how I can store these values in the database:

• As a full string, i.e facebook, email , website

• As a 1 letter code, e.g F, E, W

• As a 1 number code, e.g 1, 2, 3

Which开发者_开发技巧 approach is the best approach from a database performance / maintainence point of view? Is there going to be any impact (such as faster queries) if I stored the values as 1 letter / digit codes rather than as the full strings? This column will be used in WHERE statements.

Performance wise, ENUMS really tend to the most optimized (As the system knows the possible set of values, it uses different algos in searching etc etc). You can store the full string with ENUMS ('FACEBOOK', etc etc) and they will take only 1 byte of space per row! (provided its less than 256 enums in total). However, use enums only if you know for sure that F,E,W are the only options you will see. Adding new ENUMS is a pain as you will have to alter the table to update the enum values, and you will have to ensure the enum order.

A close second is digits. The issue with that is readability. You may end up having to keep another table to understand what each digit stands for. (Either in the database or in the application)

Varchar is the worst when it comes to speed of query, but is the most readable and managable.

Since you are going to use this field in a WHERE clause, you are better off using the numeric enumerator. It is generally true that you should index fields that will be filtered or sorted on (I say generally because you really need to verify with query execution plans whether the index is worth the overhead).

With this being said, however, an ENUM is a mess to use. If you decide down the road to change something, it is difficult to change. Also, if you pass in bad data, unless you have SQL strict on you will just end up with a NULL in that field. You also cannot add other attributes to the ENUM data such as whether it is active or legacy, etc. Finally, it is much more difficult to use the ENUM information in your applications directly (such as populating a drop-down menu with your ENUM choices).

Here is a good SO question about this topic:

MySQL ENUM type vs join tables

In the end, I would recommend that you use a joined table and a PK/FK relationship. If you want a faster query, just don't link the table in and use the index as your numeric enumerator. This fits much better into a good database design compared to using an ENUM in my opinion.

Well, if you use numbers, you might save a few CPU cycles. Which equals about how long it takes one of the neurons in your brain to talk to another. In layman's terms, unless you plan on doing it several billion times a second, it's better to go with which option is most readable and flexible, saving you many seconds on debugging effort, which is most likely longer than the saving you'll get by using numbers.