CODEWITHSUNDEEP
mysqldatabase-designenums
MCC
MCC

Reputation: 47

Using ENUM datatype in many columns with list of values is good practice?

I am developing a dating website. In that project I use a mysql table named "users" and for this table I have used ENUM datatype for many columns.

my "users" table looks like this :

CREATE TABLE IF NOT EXISTS users (
    user_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    country_id SMALLINT UNSIGNED NOT NULL,

    username VARCHAR(20) NOT NULL, 
    password CHAR(128) NOT NULL,
    salt CHAR(128) NOT NULL,
    email varchar(40) NOT NULL,

    first_name VARCHAR(30) DEFAULT NULL,
    last_name VARCHAR(30) DEFAULT NULL,
    sex ENUM('Male', 'Female') DEFAULT 'Male',
    dob VARCHAR(10) NOT NULL, 
    mobile VARCHAR(12) DEFAULT NULL,

  address VARCHAR(40) DEFAULT NULL,
  address_two VARCHAR(40) DEFAULT NULL,
  city VARCHAR(25) NOT NULL,

    whoami VARCHAR(80) NOT NULL, 
    looking_for ENUM('Short-term','Long-term','Marriage','Dating','Friends/Hang Out','Intimate Encounter','Talk/Email/Pen Pal','Activity Partner') DEFAULT 'Dating',
    ethnicity ENUM('White/Caucasian','Black/African','Hispanic/Latino','Asian','Native American','Middle Eastern','East Indian','Pacific Islander','Mixed Race') DEFAULT NULL,
    marital_status ENUM('Never Married','Divorced','Widowed','Separated','In a Relationship','Not Single/Not Looking') DEFAULT NULL,
    religion ENUM('Atheist','Buddhist/Taoist','Christian/Catholic','Christian/Protestant','Christian/LDS','Christian/Other','Hindu','Jewish','Muslim/Islamic','New Age','Non-religious','Agnostic','Spiritual but not religious','Other Religion') DEFAULT NULL, 
    body_type ENUM('Skinny','Slim/Slender','Fit/Athletic','Toned & Firm','Muscular','Ripped','Modelesque','Average','Proportional','Curvy','Few extra pounds','Full Figured','Stocky','Husky','Voluptuous','Big and Beautiful','Large','Disabled') DEFAULT NULL,
    hair_color ENUM('Auburn','Red','Stawberry Blonde','Platinum Blonde','Blonde','Dark Blonde','Light Brown','Medium Brown','Dark Brown','Black','Radically Dyed','Salt & Pepper','Partial Gray','Gray','Silver/White','Bald/Shaved') DEFAULT NULL,
    drink ENUM('Never','Rarely','Occasionally','Socially','Regularly','Gave it up') DEFAULT NULL,
    smoke ENUM('Never','Socially','Regularly','Trying To Quit') DEFAULT NULL,
    headline VARCHAR(100) DEFAULT NULL, 
    about_user TEXT DEFAULT NULL, 
    ideal_match_info TEXT DEFAULT NULL, 

    last_login TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    UNIQUE (email), 
    UNIQUE (username)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

My question is, ENUM datatype I have used in this table is correct or not. If this is the correct way hope any body guid me for right direction.

Thank you.

Upvotes: 1

Views: 625

Answers (2)

Samvel Avanesov
Samvel Avanesov

Reputation: 422

This is a really bad way of using enum because:

  1. There's no easy way to add or remove options from the website.
  2. There's no easy way to get the list of possible values from the codebehind of the website (say PHP code)
  3. It's very easy to make a mistake in the backend code when referencing the enum value
  4. It's very ease to make a mistake in database code as well.
  5. There's no easy way to implement i18n with this.

What you're looking for is a set of master-slave tables with one to many type relation.

Judging by your code you will get several more tables:

looking_for_list (int id, varchar(255) text_en, ... )
ethnicity_list (int id, varchar(255) text_en, ... )
marital_status_list (int id, varchar(255) text_en, ... )
religion_list (int id, varchar(255) text_en, ... )
body_type_list (int id, varchar(255) text_en, ... )
hair_color_list (int id, varchar(255) text_en, ... )
drink_list (int id, varchar(255) text_en, ... )
smoke_list (int id, varchar(255) text_en, ... )

And reference the values in them by id (in the master table).

Upvotes: 1

Joni
Joni

Reputation: 111239

This is not a good way to use ENUM. It's very likely you'll have to change the options, and the only way change an ENUM is with a costly and error-prone ALTER TABLE. I would store the option as text instead (VARCHAR or TINYTEXT). From a normalization perspective it would be better to create separate tables for the options but that makes the data harder to understand.

The ENUM data type is best used for cases when the options are guaranteed not to change.

Upvotes: 2

Related Questions