thara Posted May 31, 2015 Share Posted May 31, 2015 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. Quote Link to comment Share on other sites More sharing options...
requinix Posted May 31, 2015 Share Posted May 31, 2015 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. Quote Link to comment Share on other sites More sharing options...
thara Posted May 31, 2015 Author Share Posted May 31, 2015 Can I see database design for this ? Quote Link to comment Share on other sites More sharing options...
requinix Posted May 31, 2015 Share Posted May 31, 2015 (edited) 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.idNote 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.idenum_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 Quote Link to comment Share on other sites More sharing options...
Barand Posted May 31, 2015 Share Posted May 31, 2015 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"; 1 Quote Link to comment Share on other sites More sharing options...
thara Posted June 5, 2015 Author Share Posted June 5, 2015 @Barand, Can we modify above function to display selected="selected" for selected option. Quote Link to comment Share on other sites More sharing options...
Barand Posted June 5, 2015 Share Posted June 5, 2015 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"; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.