Enum datatype versus table of data in MySQL?
I have one MySQL table, users
, with the following columns:
- user_id (PK)
- name
- 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 wou开发者_StackOverflow社区ld mean one less table. I hope that makes sense, this is the first time I've attempted to describe MySQL tables in a forum.
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.
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
精彩评论