ENUM Question

[Gargouil]

hi,

suppose i have a mysql table that contains info about users and one of the field if the country.

Possibility 1:
use a numeric value like id_country then create a second table where they are listed with id_country as key

or

Possibility 2:
use enum(...) to create my country field

what is whant to know is witch one is better and witch one is smaller

when performing a select on a enum field we see the value but how in reality the true physical value was stored.. it is the text value or the index value ?

[Sxooter]

You shouldn't pick one or the other for speed so much as for data integrity and ease of maintenance.

Using a seperate table in mysql allows you to add values at a later date. I.e. if you have three countrys to start, it's easy to write an interface that adds countries to the other tables over time. Changing your DDL to say the enum can have more items is not quite as easy to do for a web app. Plus a table like that would likley have more than just the country name, it should have all the info for that country, so it doesn't get repeated elsewhere.

On the other hand, enum is more like a data constraint in other relational databases (i.e. create table test (name text check in ("bob","robert","stan"), id int8); ) It forces all the entries to be from the list and only from the list.

The enum / check in constraint setup is generally faster, but less flexible after being setup.