When/how to use ENUM?

[immanuelx2]

This might be a very noobish question, but I have always wondered how the mysql ENUM type works.

 

I understand the enumeration concept( I think)

 

Vehicle enumeration:

1=>Car 2=>Truck 3=>SUV

 

So my question is, using that above scenario, how (or better yet why) would I use the Mysql ENUM type to store that. Why not just store them as '1', '2' or '3' and let the PHP figure out what those enumerations stand for?

 

Thanks in advance

[immanuelx2]

bump.

 

I suppose my question is why I would use MySQL enums when I could use PHP to handle those, and at the same time, how are MySQL enum values stored and manipulated?

[Ken2k7]

So ENUM is just for restrictions. Say you have a field for months. You can use ENUM to only allow the list you provide. For example:

 

Table date

month ENUM('Jan', 'Feb', 'Mar' ...);

 

I didn't bother listing all 12 months, but you get the idea. So with ENUM, you can prevent entries like 1 or January because they don't match any of the ones in your list. Just use ENUM when you have a fixed array of possible values. It's just so you know that field can only have one of those values. Just added restrictions.

 

Now for your vehicle enumeration, you can use ENUM('Car', 'Trunk', 'SUV') however I would suggest you put them into another table. Up to you really.

 

Hope this clears it up a bit.

[immanuelx2]

So ENUM is just for restrictions. Say you have a field for months. You can use ENUM to only allow the list you provide. For example:

 

Table date

month ENUM('Jan', 'Feb', 'Mar' ...);

 

I didn't bother listing all 12 months, but you get the idea. So with ENUM, you can prevent entries like 1 or January because they don't match any of the ones in your list. Just use ENUM when you have a fixed array of possible values. It's just so you know that field can only have one of those values. Just added restrictions.

 

Now for your vehicle enumeration, you can use ENUM('Car', 'Trunk', 'SUV') however I would suggest you put them into another table. Up to you really.

 

Hope this clears it up a bit.

 

Thanks, this does clear up quite a bit.

So all enum does is restrict a table to the values you specify.

 

Wouldn't it be more efficient to store integers as representations of the enumerations (1-3), and have PHP parse them out(Car, Truck SUV)?

[aschk]

Honestly i wouldn't bother using ENUM as they're non-SQL92 compliant.

And if anything it just demonstrates a lack of effort regarding normalisation of data.

 

Just create a table called "vehicle_type" or something of that ilk, to store your types in, and then reference their ids in your primary table.

[fenway]

Wouldn't it be more efficient to store integers as representations of the enumerations (1-3), and have PHP parse them out(Car, Truck SUV)?

That's *precisely* what enum does.

 

But aschk is correct -- ENUM is usually a futile shortcut for non-system fields.