Well, an integer is just an integer. An enum is an indexed string, as described in the
documentation.
Basically, an enum is for when you want a column to have a string value, but only want it to be one of a given set of possibilities. For example, if you define a table like this:
SQL
CREATE TABLE items (
id INT NOT NULL PRIMARY KEY,
color ENUM('red', 'blue', 'yellow'),
... );
Then the color field will only be allowed to have the value 'red', 'blue', or 'yellow'. Trying to set any other string to that field will raise an error.
The effect is similar to a join table with a foreign key. For example:
SQL
CREATE TABLE colors (
color_id IN NOT NULL PRIMARY KEY
color VARCHAR(10) UNIQUE NOT NULL
);
CREATE TABLE items (
id INT NOT NULL PRIMARY KEY
color_id INT,
...,
FOREIGN KEY (color_id) REFERENCES colors(color_id)
);
With this, you get the same constraint - that the color has to be in a given list. The differences is that when you update a row, you have to specify an ID rather than the actual color string. (Unless, of course, you drop the integer ID and use the string as the colors key. But then you're storing your color string in the items table, which may or may not be a problem.) You still get the constraint of the column being limited to a set of know values, but you're storing them in another table instead.
Enums are fine, but personally, I tend to prefer the join table approach. From what I've read, they're not appreciably slower than enums and they're more relational and offer more flexibility. For one, it's easier to get the list of all allowed values. For another, it's much less hassle to add or remove an allowed value. For example, where I work, one of our highest-traffic tables has an enum column and it's a pain to try to add a value to it. Changing an enum requires an ALTER TABLE that locks the table, and acquiring a lock on the table anywhere near our peak hours is practically impossible. We usually end up running the ALTER TABLE in a cron job at 5:00AM. With a join table, it's just a simple INSERT or DELETE to add or remove a value.
The third option, which is perhaps what you were refering to, is just using plain-old integers as your color codes and defining the meanings for them in your code. That offers less data protection and I tend to avoid that unless I know the allowed values aren't going to change very offent and/or they are only used in a few places. Sometimes this method appropriate, but I wouldn't recommend it in the general case.
23 Jul, 2008 - 10:51 PM
| 