Mysql: enum confusion

Question

I have an employee table, employee has interests, so the table can be designed like this:

create table emp(
 id int(10) not null auto_increment,
 name varchar(30),
 interest varchar(50),
 primary key(id)
);

or this:

create table emp(
 id int(10) not null auto_increment,
 name varchar(30),
 interest enum('football','basketball','music','table tennis','volleyball'),
 primary key(id)
);

The number of interests can be about 50.

How should i design the table? Should i use enum or others ?

Edit:

Thanks for your reponse.

Assume that a person can be a Mr. or Madame or Ms.

I make a drop down list in PHP.

<select name="role">
<option value="Mr.">Mr.</option>
<option value="Ms">Ms</option>
<option value="Madame">Madame</option>
</select>

And for the DB part, I can do this:

create table emp(
    id int(10) not null auto_increment,
    name varchar(30),
    role varchar(50),
    primary key(id)
);

or this:

create table emp(
    id int(10) not null auto_increment,
    name varchar(30),
    role enum('Mr.','Ms.','Madame'),
    primary key(id)
);

In this context, which is better?

Answer 1

You should really make 3 tables, assuming that an employee can have multiple interests. (Your current design limits each employee to 1 interest.) Something like this:

Employee (emp)
-------
id
name

Interest
-------
id
description

Employee_Interest
--------
employeeID
interestID

Regarding the edit, I'd say the enum is the better of your 2 examples as it limits you to predetermined allowable values. But many would argue that you should make a lookup table (Role with id and description) even for that

Answer 2

There's a third option, creating an additional table for holding the interest values.

INTERESTS

• interest_id, int, primary key
• interest_value, string/varchar, unique constraint (to stop duplicates)

Many-to-Many Relationship?

---

However, if you want to support an employee having multiple interests you'll need a third table. This is a many-to-many relationship - the third table would sit between the EMPLOYEES and INTERESTS tables, and have foreign key relationships with both.

EMPLOYEE_INTERESTS

• employee_id, primary key, foreign key to EMPLOYEES.id
• interest_id, primary key, foreign key to INTERESTS.interest_id

One-to-Many Relationship?

---

If an EMPLOYEES record can only ever have one INTEREST, then you only need to update the EMPLOYEES.interest column to have a foreign key relationship with the INTERESTS table.

EMPLOYEES

• interest_id, primary key, foreign key to INTERESTS.interest_id