Alex Rivera | Logout

MySQL ENUM type vs join tables

Asked 2008-12-12T06:34:36.443
68

My requirement

A table needs to maintain a status column.

This column represents one of 5 states.


initial design

I figured I can just make it an integer column and represent the states using a numeric value.

  • 0 = start
  • 1 = running
  • 2 = crashed
  • 3 = paused
  • 4 = stopped

Since I don't want my app to maintain the mapping from the integers to their string description, I plan to place those in a separate state description table (relying on a FK relation).

Then I discovered that MySQL has an ENUM type which matches my requirement exactly. Other than a direct dependency on MySQL, are there any pitfalls with using the ENUM type?

mysql database-design enums
Edit
Report

1 Answer

3

If you have lots of data in your DB ( more data then you have RAM ) and you ENUM values are NEVER going to change, I would go with ENUM, rather than the join. It should be faster.
Think about it, in the join case, you need an index on your foreign key and index on your primary key in the other table. As Riho said, see the benchmarks.

answered 2010-11-18T18:23:11.710

Your Answer