What is the best way to represent a three-choice option in a database?

Question

I have got a list of objects that can be in three states only.

Simplified example of the data:

Substance State
H2O  solid
H2O  fluid
CO2  gas
...  ...

Is it better to use TINYINT(2) or ENUM or anything else? Should I insert the values right as they are (VARCHAR) or use numbers? External table of values?

Answer 1

I would reffer via an ID to an external table. THat table then shows the information as text. In your select statements, you simply have to join. If you don't know what I'm talking about, ask me for an example :)

Though, I wouldn't use ENUM, for various reasons. Find the most important ones here: http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/

Answer 2

Nothing will be stored in less than one byte.

TINYINT(2) and an ENUM with 3 members will both result in 1 byte of storage. Either will be fine.

The nice thing about ENUMs is that you can use plain English names, and they are stored compactly. The bad thing about ENUMs is that they are not ANSI SQL.