good database design: enum values: ints or strings?

Question

I have a column in a table that will store an enum value. E.g. Large, Medium, Small or the days of the week. This will correspond to displayed text on a web page or user selection from a droplist. What is the best design?

Store the values as an int and then perhaps have a table that has the enums/int corresponding string in it.

Just store the values in the column as a string, to make queries a little more self-explanatory.

At what point/quantity of values is it best to use ints or strings.

Thanks.

Answer 1

Assuming your RDBMS of choice doesn't have an ENUM type (which handles this for you), I think best to use ids instead of strings directly when the values can change (either in value or in quantity.)

You might think that days of the week won't change, but what if your application needs to add internationalization support? (or an evil multinational corporation decides to rename them after taking control of the world?)

Also, that Large, Medium and Small categorization is probably changing after a while. Most values you think cannot change, can change after a while.

So, mainly for anticipating change reasons, I think it's best to use ids, you just need to change the translation table and everything works painlessly. For i18n, you can just expand the translation table and pull the proper records automatically.

Most likely (it'll depend on various factors) ints are going to perform better, at the very least in the amount of required storage. But I wouldn't do ints for performance reasons, I'd do ints for flexibility reasons.

Answer 2

this is an interesting question. Definitely you have to take in consideration performance targets here. If you wan't to go for speed, int is a must. A Database can index integers a bit better than Strings although I must say its not at all a bad performance loss.

On example is Oracle database itself where they have the luxury of doing large caps enum as strings on their system tables. Things like USER_ALLOCATION_TYPE or things like that are the norm. Its like you say, Strings can be more "extensible" and more readable, but in any case in the code you will end up with:

Static final String USER_ALLOCATION_TYPE="USER_ALLOCATION_TYPE";

in place of

Static final int USER_ALLOCATION_TYPE=5;

Because you either do this you will end up with all this string literals that are just aching for someone to go there and misplace a char! :)

In my company we use tables with integers primary keys; all the tables have a serial primary key, because even if you don't think you need one, sooner or later you'll regret that.

In the case you are describing what we do is that we have a table with (PK Int, Description String) and then we do Views over the master tables with joins to get the descriptions, that way we get to see the joined fields descriptions if we must and we keep the performance up.

Also, with a separate description table you can have EXTRA information about those ids you would never think about. For example, lets say a user can have access to some fields in the combo box if and only if they have such property and so. You could use extra fields in the description table to store that in place of ad-hoc code.

My two cents.