3

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?

aelita
  • 33
  • 1
  • 7

4 Answers4

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.

Eric J.
  • 147,927
  • 63
  • 340
  • 553
  • I'm sorry, I am very new to the stuff. > The nice thing about ENUMs is that ... they are stored compactly. A lot more compactly? I've got about 10 fields like this in my database, is it a lot better to store choices in ENUMs rather than in external tables? – aelita Jul 10 '12 at 06:29
  • If you have 1 to 255 choices, it MySQL will use 1 byte. If you have 256 to 32,767 choices, MySQL will use 2 bytes. – Eric J. Jul 10 '12 at 06:31
  • I meant another kind of field. E.g. state(solid,fluid,gas), color(red,green,blue), size(small,medium,large) etc. – aelita Jul 10 '12 at 06:38
2

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/

PoeHaH
  • 1,936
  • 3
  • 28
  • 52
0

Create a master table as state(id int , desc varchar), According to the problem it will have 

id   |  desc
1    |  Solid
2    |  Liquid
3    |  Gas

and in the solution(main) table refer the state table's id

Sashi Kant
  • 13,277
  • 9
  • 44
  • 71
0

I think you can store the information in a column with type BIT and allow null values in the

column. The following is the mapping of value and your data.

H2O solid --> TRUE

H2O fluid --> FALSE

CO2 gas --> NULL

Narendra
  • 3,069
  • 7
  • 30
  • 51
  • Any reason why this got a down?? – Narendra Jul 10 '12 at 06:35
  • 1
    Possibly because there is zero difference between BIT and TINYINT, while being overly hackish. ENUM is still the better overall answer for a few values. – SilverbackNet Jul 10 '12 at 06:40
  • Yes, but whats wrong in using BIT?? http://stackoverflow.com/questions/488811/tinyint-vs-bit – Narendra Jul 10 '12 at 06:47
  • 2
    Because using NULL to represent an actual value will land you in TDWTF for well-deserved mockery someday. For actual binary columns, no problem, unless they're extended. – SilverbackNet Jul 10 '12 at 06:50
  • And because with near certainty the requirement will pop up next week to store "CO" and you are stuck. All the reports that assume the nullable BIT have to be redone, etc... – Eric J. Jul 10 '12 at 16:33