1

I am writing code (using MySQL) to solve a problem similar to the following:

There are 20 boolean options (per every user).

Should I store 20 ENUM('false','true') or put into a table only IDs of these options which are true (so probably having less than 20 rows per user)?

porton
  • 5,214
  • 11
  • 47
  • 95
  • 1
    Are the new options ever likely to appear? Do you need to filter your users by several options at once (like `option1 = TRUE AND option2 = TRUE`)? Are all options set for every user? Which percent of users have non-default values of the options set? – Quassnoi Jan 17 '13 at 10:14
  • 1
    Why not create a table containing your options and having an `option_id`, next get a link table `user_options` containing `user_id`, `option_id` and `condition`, and lastly having your `users` table with your `user_id`? That way, when you add options, you dont need to redesign your `users` table – Tikkes Jan 17 '13 at 10:17
  • @Quassnoi: New options are likely to appear. I never filter users by options, I just show values of all 20 options when a user is selected. For every user every option should be defined (either true or false), with possible exception when we add new kind of option (the options could reasonably be false by default in this case). The percent of users with non-default values may be probably around 50%. – porton Jan 17 '13 at 10:19
  • Possible duplicate of http://stackoverflow.com/questions/289727/which-mysql-datatype-to-use-for-storing-boolean-values – SchmitzIT Jan 17 '13 at 10:19
  • @Tikkes: What you've suggested is just exactly one of the two ways I consider. The other way is to remove condition column and put only these options_id which are true. – porton Jan 17 '13 at 10:21
  • And what happens when you add options? You have to mess around with your table structure. That is not a good approach in my opinion. – Tikkes Jan 17 '13 at 10:22
  • Quassnoi makes an important point - if the options tend to be sparsely populated (only a few non-default values per user) and if more options might need to be added in the future, the best way to go about this would be to store only the options that are true (or non-default), each one in its own row - (id1,optionId2), (id2,optionId13) etc.. – Noam Kremen Jan 17 '13 at 10:33

5 Answers5

4

If new options are likely to appear and you don't filter by the options, you may as well go with a EAV structure (a record per option).

This way, you can add new options more easily (no change to metadata).

Assuming that the options values are either TRUE or FALSE (no NULL possible), you should create records only for non-default option values (TRUE in your case). An absence of the record would mean false.

To retrieve all options, you could use this:

SELECT  *, GROUP_CONCAT(CONCAT(o.id, ': ', ov.user IS NULL), ', ' ORDER BY o.id)
FROM    users u
CROSS JOIN
        options o
LEFT JOIN
        option_value ov
ON      (ov.user, ov.option) = (u.id, o.id)
GROUP BY
        u.id

, which would give you dynamic output:

user_id   options
1         1: 0, 2: 1, 3: 0
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • What is EAV? Should I add a row for EVERY user, when a new options are added? This would be simpler to implement if we store rows only for true (not for false) options. – porton Jan 17 '13 at 10:40
  • @porton: http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model. If you adding a new option, most probably the users will have the default value for this option which is marked with an absence of value in `option_value`. Thus, you should only add a single record to `options` with the new option's id and name. – Quassnoi Jan 17 '13 at 10:47
3

I'd suggest creating an Options table with the different options.

+---Options---+
ID
Option

+---Users---+
ID
Name

+---User_Options---+
User_id
Option_id

Now if you need more options, insert them into the Options table, you dont need to alter your database this way.

EDIT: Removed condition in user_options: like Quassnoi mensioned, it would be better to just add records in case of "TRUE" and the absence of a record should be considered "FALSE"

Tikkes
  • 4,599
  • 4
  • 36
  • 62
1

I would recommend storing as a TINYINT 0 or 1. many frameworks work out of box with the TINYINT data type and handle it as a boolean.

Headshota
  • 21,021
  • 11
  • 61
  • 82
0

Create 3 tables . First one is 'user_table' . It contain username and user_id. Sample data is given below

enter image description here

Table create script is given below

Now create another table called options_table. It contain option_name and option_id for each option. Sample is given below

enter image description here

Now create a third table called 'selected_options'. That table maps user to options. It contain user_id and option_id

Sample is given below

enter image description here

In the above example user1 selected option1 and option2

and user2 selected option1,option2 and option3 ie..option1,option2 and option3 are true for user2

Fathah Rehman P
  • 8,401
  • 4
  • 40
  • 42
0

I would recommend using a bitmask column. If you have numerous options, rather than creating a new column per option, you would be able to quickly perform bit-wise comparisons.

For additional info, see:

SELECT users from MySQL database by privileges bitmask?
Implement bitmask or relational ACL in PHP
Using bitmasks to indicate status

Community
  • 1
  • 1
SchmitzIT
  • 9,227
  • 9
  • 65
  • 92