enum vs seperate table

tunage

Hello

Im currently redesigning my database and wondering if it would be better to use enum or create a seperate table.

Each product volume has a price type of 'tier', 'bulk', or 'set' - therefore im thinking itd be easiest to just use enum('tier', 'bulk', 'set') rather than creating a seperate table and recording the price type id.

eg -

ID volID type qty price
1 | 1 | tier | 1 | 10
2 | 1 | tier | 3 | 9
3 | 1 | tier | 8 | 8
4 | 2 | set | 1 | 50
5 | 2 | set | 2 | 90

6 | 2 | set | 3 | 120

Are there any major performance issues when using enum compared to a seperate table, as far as i can see its storing a few more letters in that field (rather that just 1 number) which will make the database larger but queries will be quicker as there will be one less join to do.

Also, if I use enum('t', 'b', 's') instead of the entire word, would this be a better idea or would there not really be any difference?

(Im using MySQL)

devinemke

generally speaking, if the option set is small, not likely to change often and does not rely on other table realtionships, then use enum. there is really no difference between using enum('tier','bulk','set') vs. enum('t', 'b', 's') since each record only stores the integer reference to the enum options.

paulnaj

Hi,

Originally posted by devinemke
... and does rely on other table realtionships, then use enum. ...

Can I ask what's the reasoning behind that?

Cheers,
Paul 🙂

devinemke

Originally posted by paulnaj
Hi, Can I ask what's the reasoning behind that?

yes, you may ask, and the answer is a simple typo. i meant to say "does not" rather than "does" (fixed above).

Sxooter

Given the fact that enum provides no real error checking on the type, the enum type is likely to lead to data errors.

If you want to make sure the user chooses one and only one option for a field in the table, then make a table with the types in it and foreign key the field that would have been enum to it. You have to use innodb to have fk relations I think.

I mean, you can get the rather non-intuitive behavior like this from enum:

mysql> create table test (id int, pick enum('abc','123','2'));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+------+
| id | pick |
+------+------+
| 1 | 123 |
+------+------+
1 row in set (0.00 sec)

Wait asecond, didn't I specify 2? Oh, yeah, if you insert a number instead of the text with quotes, it uses the number it got.

Ugh.

csn

Plus with enums there isn't any easy way to retrieve all its values (for populating an option box, for example).

devinemke

Originally posted by csn
Plus with enums there isn't any easy way to retrieve all its values (for populating an option box, for example).

sure there is, just do a DESCRIBE query.

csn

Originally posted by devinemke
sure there is, just do a DESCRIBE query.

Post an example. One that populates an array with the enum values.

csn

Originally posted by devinemke
sure there is, just do a DESCRIBE query.

And DESCRIBE doesn't appear to be database-independent.

Sxooter

While I'm quite sure I could hack something up, I more than get the point that getting all the possible choices for an enum type is harder than it should be, and MUCH harder than the select * from optable type query one would use if one had an external table holding all those values.

But that's not the REAL reason to not use enum, it's the fact that it can get the wrong answer all too easily. But now that I think of it, that's a reason to not use MySQL in general. 🙂

csn

Yeah, just use a regular expression. Check this out:

mysql> describe wp_comments;
+----------------------+------------------+------+-----+---------------------+----------------+
| Field                | Type             | Null | Key | Default             | Extra          |
+----------------------+------------------+------+-----+---------------------+----------------+
| comment_ID           | int(11) unsigned |      | PRI | NULL                | auto_increment |
| comment_post_ID      | int(11)          |      | MUL | 0                   |                |
| comment_author       | tinytext         |      |     |                     |                |
| comment_author_email | varchar(100)     |      |     |                     |                |
| comment_author_url   | varchar(200)     |      |     |                     |                |
| comment_author_IP    | varchar(100)     |      |     |                     |                |
| comment_date         | datetime         |      |     | 0000-00-00 00:00:00 |                |
| comment_date_gmt     | datetime         |      |     | 0000-00-00 00:00:00 |                |
| comment_content      | text             |      |     |                     |                |
| comment_karma        | int(11)          |      |     | 0                   |                |
| comment_approved     | enum('0','1')    |      | MUL | 1                   |                |
| user_id              | int(11)          |      |     | 0                   |                |
+----------------------+------------------+------+-----+---------------------+----------------+
12 rows in set (0.03 sec)

Yuck.

I guess the equivalent of ENUM in Postgres is constraints. Is there an easy way to fetch the values of a constraint - probably by querying pg_constraint?

csn

This narrows it down:

mysql> describe wp_comments comment_approved;
+------------------+---------------+------+-----+---------+-------+
| Field            | Type          | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| comment_approved | enum('0','1') |      | MUL | 1       |       |
+------------------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

Sxooter

Originally posted by csn

I guess the equivalent of ENUM in Postgres is constraints. Is there an easy way to fetch the values of a constraint - probably by querying pg_constraint?

While you could use a check constraint, it's just as easy to create a table with all the options in it and fk the field in the main table to it.

If you used a check <field> in (<fieldlist>) construct, you could check it in postgresql with:

SELECT 
    pg_catalog.pg_get_constraintdef(r.oid, true), 
    conname
FROM 
    pg_catalog.pg_constraint r
WHERE 
    r.conrelid = '304203897' 
AND 
    r.contype = 'c'

where conrelid was the oid of the table in question. One could join it with another query to enable one to only use the table name for such a query.

I think the options table is the more relationally correct answer, and it allows for easy changes in what is or is not allowed in the table.

devinemke

Originally posted by csn
Post an example. One that populates an array with the enum values.

ok. a mySQL example:

$result = mysql_query('DESCRIBE table enum_field');
$row = mysql_fetch_assoc($result);
$enum = str_replace('enum', '$enum = array ', $row['Type']);
eval($enum . ';');
print_r($enum);