MySQL Tri-state field

Question

I need to create a good/neutral/bad field. which one would be the more understandable/correct way.

1. A binary field with null (1=good, null=neutral, 0=bad)
2. An int (1=good, 2=neutral, 3=bad)
3. An enum (good, neutral, bad)
4. Any other

It's only and informative field and I will not need to search by this.

Answer 1

NULL values should be reserved for either:

• unknown values; or
• not-applicable values;

neither of which is the case here.

I would simply store a CHAR value myself, one of the set {'G','N','B'}. That's probably the easiest solution and takes up little space while still providing mnemonic value (easily converting 'G' to 'Good' for example).

If you're less concerned about space, then you could even store them as varchar(7) or equivalent and store the actual values {'Good','Neutral','Bad'} so that no translation at all would be needed in your select statements (assuming those are the actual values you will be printing).

Answer 2

In Mysql you ought to be using an enum type. You can pick any names you like without worrying about space, because Mysql stores the data as a short integer. See 10.4.4. The ENUM Type in the documentation.