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

[thara]

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.

[requinix]

What happens when you want to add a new religion? Or hair color?

Use lookup tables. Each has an ID number and a value, like 1/Auburn and 5/Regularly. You could actually combine the tables into one by adding a column indicating what the value is (eg, 1/hair color/Auburn).

 

But let's keep going. What happens when you want to add a new question? Or remove one?

Use another table. It works best with the combined-tables version above: one table with an ID and question, then the second table with an ID, question ID, and answer. The answers themselves then move out of the user into and into a third table containing the user ID and answer ID.

[thara]

Can I see database design for this ?

[requinix]

For arbitrary questions and answers of various types (which is more than I initially described),

questions

id | type | question
---+------+--------------------------------------------------
 1 | text | Describe yourself.                                /* whoami */
 2 | enum | What kind of relationship are you looking for ?   /* looking_for */
 3 | enum | What is a polite, PC way of asking for ethnicity? /* ethnicity */

question_enums

id | questionID | answer
---+------------+-----------
 1 |          2 | Short-term
 2 |          2 | Long-term
 3 |          2 | Marriage

answers

id | userID | questionID | answer_text | answer_enum
---+--------+------------+-------------+------------
 1 |      1 |          1 | text here   |        NULL /* user #1 describes themself as "text here" */
 2 |      1 |          2 | NULL        |           3 /* user #1 wants marriage */
 3 |      1 |          3 | NULL        |          15 /* user #1 is east indian */

- All questions are either free-form text or multiple choice (that column would be an appropriate place to use ENUM)

- Arbitrary list of questions

- Arbitrary list of enum values

 

Query to get just someone's answers can go like

SELECT q.question, q.type, a.answer_text, qe.answer AS answer_enum
FROM questions q
LEFT JOIN answers a ON q.id = a.questionID
LEFT JOIN question_enums qe ON q.id = qe.questionID AND q.type = "enum" AND a.answer_enum = eq.id
WHERE a.userID = $userID
ORDER BY q.id

Note the outer joins:

- LEFT JOIN answers because the user may not have answered a question yet

- LEFT JOIN question_enums because a question may not be an enum type

 

If you make sure that enum answers do not contain a particular character, like the pipe |, then it's easy to get all answers with the questions too, such as when the user is answering the questions:

SELECT
	q.question,
	q.type,
	a.answer_text,
	a.answer_enum,
	GROUP_CONCAT(CONCAT(qe.id, ",", qe.answer) ORDER BY qe.id SEPARATOR "|") AS enum_answers
FROM questions q
LEFT JOIN answers a ON q.id = a.questionID
LEFT JOIN question_enums qe ON q.id = qe.questionID AND q.type = "enum"
WHERE a.userID = $userID
GROUP BY q.id
ORDER BY q.id

enum_answers would look like "1,Short-term|2,Long-term|3,Marriage", and you can use a bit of PHP code to break that back apart into the individual answers with their IDs.

 

Obvious places for improvement:

- Sorting questions, like by a simple integer field that you then ORDER BY

- Likewise, sorting enum answers because sometimes there's a obvious ordering that should be used (eg, level of education)

- Partitioning questions into "groups" or "pages"

- Text questions should have length limits, like 100 for short answers and 1000 for long answers, that should vary by question

- Allowing multiple answers for an enum (as in "pick all that apply")

Edited May 31, 2015 by requinix

[Barand]

I prefer the use of lookup tables and using the foreign key in the data.

• It is much easier to create dropdown selection menus
• It enforces referential integrity

If you want to stick with enum and need to get the options available for dropdowns in your forms, this function will do it for you

function enumOptions($db, $table, $colname)
    /**********************************
    * db      - database connection
    * table   - table name
    * colname - name of the ENUM column
    ***********************************/
{
    $sql = "SELECT SUBSTRING(COLUMN_TYPE,5) as vals 
            FROM information_schema.COLUMNS
            WHERE table_schema = (SELECT DATABASE())
            AND table_name='$table'
            AND column_name = '$colname'";
    $res = $db->query($sql);
    list($vals) = $res->fetch_row();
    $vals = explode(',', trim($vals, '()'));
    $opts = "<option value=''>- select $colname -</option>\n";
    foreach($vals as $k => $val) {
        $opts .= sprintf("<option value='%d'>%s</option>\n", $k+1, trim($val, "'"));
    }
    return $opts;
}

// EXAMPLE USAGE
echo "<select name='status'>\n";
echo enumOptions($db, 'users', 'status');
echo "</select>\n";

[thara]

@Barand, Can we modify above function to display 

selected="selected"

for selected option.

[Barand]

Pass the current value to the function and test for it when creating the options

function enumOptions($db, $table, $colname, $current='')
    /**********************************
    * db      - database connection
    * table   - table name
    * colname - name of the ENUM column
    * current - current selected value
    ***********************************/
{
    $sql = "SELECT SUBSTRING(COLUMN_TYPE,5) as vals 
            FROM information_schema.COLUMNS
            WHERE table_schema = (SELECT DATABASE())
            AND table_name='$table'
            AND column_name = '$colname'";
    $res = $db->query($sql);
    list($vals) = $res->fetch_row();
    $vals = explode(',', trim($vals, '()'));
    $opts = "<option value=''>- select $colname -</option>\n";
    foreach($vals as $k => $val) {
        $sel = (($k+1==$current) || (trim($val, "'")==$current)) ? 'selected="selected"' : '';
        $opts .= sprintf("<option $sel value='%d'>%s</option>\n", $k+1, trim($val, "'"));
    }
    return $opts;
}

// EXAMPLE USAGE
echo "<select name='status'>\n";
echo enumOptions($db, 'users', 'status', '1');
echo "</select>\n";