asked
Look up tables in the database
Guys, what is the best way of handling Enums in the .Net or Java in the sql server 2008 database. I am thinking.. to use lookup table and create a constraint between those the lookup tabel and the main table. Is there any other efficient way to handle this issue?
I would go with a lookup table and [foreign key][1], as you mention, for more complex examples. Another way (perhaps more suitable for smaller Enums) might be to use a [check constraint][2]. [1]:
http://msdn.microsoft.com/en-us/library/ms177463.aspx [2]:
http://msdn.microsoft.com/en-us/library/ms179491.aspx
I would still prefer a lookup table to a check constraint because it documents the meaning of the numbers. I hate trying to find out what WHERE Status = 2 means. If it's in a table everyone knows.
Really small Enums (no more than 3 values). Other than that, yup.
I would use lookup tables, because the possibility to add more values without a release and without involvment of the DBA. :) The DBA usually have enough to do anyway.
@William - quite right. The query about performance was just one of those idle curiosity things that sometimes get the better of me! It's easier for someone else to maintain a lookup table. You can quite easily (and relatively safely) give rights and tools to the users to do this sort of thing themselves - but I wouldn't want to give a user rights to the schema itself!
Erm, it depends. +1 to Thomas for a general approach - but... It depends on what the enum is, really. If it is an un-numbered enum i.e. something like: public enum Animal { Dog, Cat, LesserSpottedDBA } Then yes, a lookup table is the best way to go, because you can't tie a particular animal to a particular value. One thing to watch out for, however is that calling ToString() on an Enum value may not work if you run it under obfuscation, because Enum.ToString() uses reflection internally. So, be careful to exclude enum types where you want to store them by name from being obfuscated. However, for numbered enums... public enum Animal { Dog = 0, Cat = 1, LesserSpottedDBA = 2 } Then you have a strong correlation between the number and it's meaning, in which case it can be a lot more efficient to simply store the number. Use the smallest integer type that covers the range of the enum. Your validity constraint in this case would be that the value falls between the minimum and maximum values of the enum. In the case of an enum with gaps, then you are probably best off going the lookup table route. Then you have [`[Flags]`][2] enums. Now these actually *cannot* be stored as a foreign key reference, unless you create a table which contains all possible combinations of the enum. Consider the [`StringFormatFlags`][1] enumeration. This one stores flags - or an enumeration where each of the members is a power of 2, and the resulting value is a combination of one or more members. This enum tops out at 16384 - which is modest - meaning you would only need 32768 rows in your enum lookup table to cater for it. However, there are other enums which are [`[Flags]`][2] enums which top out much higher than that, and the lookup table approach would then become wholly inappropriate. For these, I would again use an integer type, choosing the smallest type which covered all combinations of set bits. Here your constraint is `[value] >= 0 AND [value] < ([highest_value] * 2) - 1`. In a [`[Flags]`][2] enumeration gaps should not matter as much, because an extra bit that is set *should* have no meaning to the application, as it should only test the specific bits that it knows about. Be careful though, if *should* was equal to *does* then most of us probably wouldn't have jobs, and computers would be a lot less annoying! [1]:
http://msdn.microsoft.com/en-us/library/system.drawing.stringformatflags.aspx [2]:
http://msdn.microsoft.com/en-us/library/system.flagsattribute.aspx
