I've got a column inside a table which will store an enum value. E.g. Large, Medium, Small or the era of the week. This can match displayed text on the web site or user selection from the droplist. What's the best design?
Keep values being an int after which possibly possess a table which has the enums/int corresponding string inside it.
Just keep values within the column like a string, to create queries a bit more self-explanatory.
WhenOramount of values do you need to use ints or strings.
Presuming your RDBMS of preference does not come with an ENUM type (which handles this for you personally), I believe better to use ids rather than strings directly once the values can alter (in both value or perhaps in quantity.)
You may think that days each week will not change, but let's say the application must add internationalization support? (or perhaps an evil multinational corporation decides to relabel them after taking charge around the globe?)
Also, that Large, Medium and Small categorization is most likely altering before long. Most values you believe cannot change, can alter before long.
So, mainly for anticipating change reasons, I believe it is best to use ids, you need to simply alter the translation table and everything works easily. For i18n, you can easily expand the translation table and pull the correct records instantly.
Probably (it'll rely on various factors) ints are likely to perform better, at the minimum in the quantity of needed storage. However I wouldn't do ints for performance reasons, I'd do ints for versatility reasons.
Choosing the first example. Allows say you produce a Lookup table: Dimensions. It's the next posts: Id - primary key + identity Title - varchar / nvarchar
You'd have three rows within the table, Small, Medium and enormous with values 1, 2, 3 should you placed them for the reason that order.
For those who have another table that utilizes individuals values you should use the identity value because the foreign key...or you might produce a third column the industry short hands value for that three values. It might possess the values S, M &lifier L. You could utilize that because the foreign key rather. You'd have to produce a unique constraint around the column.
So far as the dropdown, you could utilize each one because the value behind the curtain.
You might create S/M/L value because the primary key too.
For the other question about when its better to make use of the ints versus strings. There's most likely lots of debate about them. Many people only like using identity values his or her primary secrets. Others state that it's better to utilize a natural key. If you're not utilizing an identity because the primary key then it is just vital that you make certain you've got a good candidate for that primary key (ensuring most commonly it is unique which the worthiness doesn't change).
it is really an interesting question. Certainly you need to think of performance targets here. Should you wan't to choose speed, int is essential. A Database can index integers a little much better than Strings although I have to admit it is not whatsoever a poor performance loss.
On example is Oracle database itself where they've the posh to do large caps enum as strings on their own system tables. Such things as USER_ALLOCATION_TYPE or such things as which are standard. Its as if you say, Strings could be more "extensible" and much more readable, but regardless within the code you'll finish track of:
Static final String USER_ALLOCATION_TYPE="USER_ALLOCATION_TYPE"
Static final int USER_ALLOCATION_TYPE=5
Since you either do this you'll finish track of all of this string literals which are just aching for somebody to visit there and misplace a char! :)
During my company we use tables with integers primary secrets all of the tables possess a serial primary key, because even when you do not want one, eventually you'll regret that.
Within the situation you're explaining what we should do is the fact that there exists a table with (PK Int, Description String) therefore we do Sights within the master tables with joins to obtain the explanations, this way we see the became a member of fields explanations when we must and that we keep your performance up.
Also, having a separate description table you could have EXTRA details about individuals ids you'd never consider. For instance, allows say a person can get access to some fields within the combo box if and only when they've such property and thus. You could utilize extra fields within the description table to keep that instead of ad-hoc code.
My two cents.