CODEWITHSUNDEEP
sqlmysqldatabase-designdata-modelingenums
SUN Jiangong
SUN Jiangong

Reputation: 5312

Mysql: enum confusion

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?

Upvotes: 2

Views: 624

Answers (2)

froadie
froadie

Reputation: 83213

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

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332791

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

Upvotes: 5

Related Questions