When designing a database, is it better in the long run to use an ENUM or to use an TINY INT value to lookup a value in another table?

Specifically, I have a table where I will (currently) have three values for 1 of the fields: Positive, Neutral, Negative
I can enter them in an enum field or, how difficult is it in the future if I set it up like this:

TABLE 1
Field: ASSESSMENT TINYINT
other.....

TABLE2
Field: ASSESSID TINYINT
Field: ASSESSMENTDESCRIPTION VARCHAR(10)


Opionions?

opinions? you betcha

ENUM is the spawn of the devil

go with your lookup table

I suppose if you have a defintiive closed set of values then an enum may make sense. Ie you know that there are never ever going to be changes to the enumeration then an enum could work. it may also be an advatage if its a relatively simple table design and your users have directg access to the server for querying purposes and needed to see a textual representation of the value and were unabale or untrusted to make joins.

However even in these circumstances the theoretical advantages of the enum over a lookiup table are so marginal that I've never used one yet. Mainly because there is always at least one user out there who deciodes in a year or two, "it'd be really nice to have something else added to this enum".

There may be some infinitessimal performance advantage in using enums, but I suspect its unindeitifiable.

In short, given a choice don't use enums.

r937: I've seen you make several comments about how bad ENUMs are, but you never say why. Would you please explain why you think ENUMs are bad?



.

have you read the mysql.com documentation on ENUM type?

do yourself a favour, get your s.o. on the phone, and read a few paragraphs out loud, about the enumeration indexes and stuff, especially the part where they say "because this can easily become confusing" and see what sort of reaction you get

besides, it's non-standard (i.e. not portable to other databases systems), and i dislike non-standard stuff as a rule

but most importantly, and since i answered your question you gotta answer mine, please take a moment explain to us the ripple effect on application code when you want to remove a value that collates lower down in the index structure than other existing values

spawn of the devil, i say

I had read the page you reference from the MySQL manual. I just went back and re-read it. I guess because my primary background is in programming languages and systems, I don't find that explanation as confusing as you do. I understood the implications of re-naming values to other values because they fall at differing places in the ENUM index.

This just looks like a definition of a language function to me. But, ... I can see where it would be confusing to other people. The redefinition of terms (particularly primitive terms like the definition of 0, 1, 2, etc) is always a bad thing. I don't use ENUMs myself and can not think of any circumstance where I would want to use numbers in an ENUM. I think the example MySQL has given of changing the values of numbers by assigning them to an ENUM is a good example of a bad use of the construct.

I think you have a good argument when you say, "it's non-standard (i.e. not portable to other databases systems), and i dislike non-standard".

I don't really understand the question you asked because, I'm still fairly new to programming databases. What I understand you to be asking is the following:

I have an ENUM ('Red', 'Green', 'Blue', 'Purple', 'Yellow', 'Cyan', 'Magenta', 'Orange'). If at some point we decide that 'Purple' has no use and we want to remove it from the ENUM, what is the affect on the program?

As far as I can tell, as long as all references in the program refer to the lexical values of the set (i.e. 'Red', 'Cyan', etc) there should be absolutely no impact on the program. However, if somewhere in the program, the ENUM is referred to using the numeric values, then each time a change is made to the ENUM, we would have to inspect every usage of the table in the program to make sure we haven't broken the program.

If this is the effect you're talking about then I begin to see why you think the use of ENUMs can be a bad practice. I feel that using the numeric value to access the elements of ENUM is a bad programming practice and should never be used whether in a database or in writing a C program.

The one time I can think of when it might be permissible is if you want to create a loop in a program that will access every value of the ENUM and you say something like "for (i=0; i<=max_enum; i++)". However, as soon as you want to refer to a specific value in the ENUM, you should translate to the lexical representation of the element and only refer to it that way.

It seems to me that your objection to ENUMs comes down to the potential for bad practices and the fact that they are non-standard. Both valid reasons for being careful with them.



.

i'm not at home at the moment (where my mysql database is), so i cannot reference my notes and test results, but a test is fairly easy to set up

create a table with an ENUM for the colour values you cited, and populate the table with a few rows, various colours

now remove purple, and tell me what actually happens

not to the code, but to the table!

does it or does it not have to update each row (and not just the ones which are set to purple)?

imagine doing that to a production database of a few million rows

"gee, boss, i know it sounds dumb, but we have to take our web site off line for a couple of hours because we no longer offer purple widgets"

so then what happens? an executive decision is made, and you don't actually remove purple, but instead, you prevent purple from being selected using application code

eeewwwww

OK, the light bulb goes on!. I had not considered that aspect of it.

I see what you're saying. When we remove 'Purple', MySQL will automatically re-index the numeric values of the ENUM. This will have to propagate to every row in the table that uses the ENUM.

Whereas if we use a second small table with an id for each value, we can remove 'Purple' and don't have to re-index the ids for colors, therefore, we don't have to update the rows that refer to the color-ids.


Thanks for the discussion. That makes a lot of sense now.



.

that is correct, except of course for those rows which refer to purple, you have to change them to something else (or remove them) before you are allowed to remove purple from the lookup table (this is called enforced relational integrity)

Thanks for the input.
I'm used to coding in VB, Oracle, Access, Fox ( loved that one) and others.
From my Data Warehouse experience I knew Lookups are the way to go, however with smaller defined choices I can see the potential value of enums.