我应该如何存储只有几个可能值的实体？

Question

How should i store data like users's gender, religion, political views which is selecting from a list of 2-8 max values like 'male', 'female' or 'orthodox', 'muslim','judaism','catholic' etc? Also this values is constant, even admin cannot change 'female' to something else. In a Database it looks wierd to store a similar tables with only this 2-8 values and make JOIN with a parent table on foreign key. Second way - special object inside program code - but it's always bad to mix program logic with a data.

Answer 1

Whether or not something is looking "weird" depends on personal preferences or design structures. However, it is entirely logical to store anything in a database that has to do with, well, data. Even a given set of options can change in the distant or not so distant future. I can't count the times a client asked me to change a set of options a day, a week, or even a few years after having ensured me that the set wouldn't change, ever.

Storing a list of options in a separate table is part of a relational database design. Relational database designs make it easy to get a set of data which includes or even excludes the options in any way in my opinion.

I'd recommend doing it the good, old fashioned way, for example:

1. Table user (id, user_name)
2. Table option (id, option_label)
3. Table user_option (id, user_id, option_id)

A user that is both male and catholic would have a relation with two options:

Table user          Table option           Table user_option
+----+-----------+  +----+--------------+  +----+---------+-----------+
| id | user_name |  | id | option_label |  | id | user_id | option_id |
+----+-----------+  +----+--------------+  +----+---------+-----------+
|  1 | john      |  |  1 | male         |  |  1 |       1 |         1 |
|  2 | melody    |  |  2 | female       |  |  2 |       1 |         6 |
|  3 | gerald    |  |  3 | orthodox     |  +----+---------+-----------+
+----+-----------+  |  4 | muslim       |
                    |  5 | judaism      |
                    |  6 | catholic     |
                    +----+--------------+

Showing all selected options per user can be done with the following query:

SELECT `u`.*, GROUP_CONCAT( `o`.`option_label` SEPARATOR ', ' ) AS `options`
FROM `user` AS `u`
LEFT JOIN `user_option` AS `uo` ON `uo`.`user_id` = `u`.`id`
LEFT JOIN `option` AS `o` ON `uo`.`option_id` = `o`.`id`