CODEWITHSUNDEEP
mysqldatabasedatabase-design
Mike Moore
Mike Moore

Reputation: 7468

Enum datatype versus table of data in MySQL?

I have one MySQL table, users, with the following columns:

  1. user_id (PK)
  2. email
  3. name
  4. password

To manage a roles system, would there be a downside to either of the following options?

Option 1:

Create a second table called roles with three columns: role_id (Primary key), name, and description, then associate users.user_id with roles.role_id as foreign keys in a third table called users_roles?

Or...

Option 2:

Create a second table called roles with two columns: user_id (Foreign key from users.user_id) and role (ENUM)? The ENUM datatype column would allow for a short list of allowable roles to be inserted as values.

I've never used the ENUM datatype in MySQL before, so I'm just curious, as option 2 would mean one less table. I hope that makes sense, this is the first time I've attempted to describe MySQL tables in a forum.

Upvotes: 7

Views: 1327

Answers (2)

Max
Max

Reputation: 2643

Using ENUM for the case You suggested only makes sense when You have a strictly definded ORM on the receiving end that for istance maps db rows into a list of flat objects automatically.

Example: table animal( ENUM('reptiles','mamals') Category, (varchar 50)Name );

is automatically maped to

object animal animal->Category animal->Name

Upvotes: 0

Daniel Vassallo
Daniel Vassallo

Reputation: 344301

In general, ENUM types are not meant to be used in these situations. This is especially the case if you intend to cater for the flexibility of adding or removing roles in the future. The only way to change the values of an ENUM is with an ALTER TABLE, while defining the roles in their own table will simply require a new row in the roles table.

In addition, using the roles table allows you to add additional columns to better define the role, like the description field you suggested in Option 1. This is not possible if you were to use an ENUM type as in Option 2.

Personally I would not opt for an ENUM in these scenarios. Maybe I can see them being used for columns with an absolutely finite set of values, such as {Spades, Hearts, Diamonds, Clubs} to define the suit of a card, but not in cases such as the one in question, for the disadvantages mentioned earlier.

Upvotes: 9

Related Questions