Best way to store "choose all that apply" type questions.

[eluser]jleequeen[/eluser]
I'm building a new CI app that needs a couple paper forms converted over. The paper form has several questions that are "choose all that apply". Some of these questions have upwards of 15 options. What is the best way to store these in the database? Multiple fields for each or one field that stores an array? If one field is used, how would querying the data be approached?

Thanks.

[eluser]WanWizard[/eluser]
I've used two implementations in the past, depending on the situation.

I sometimes use a large enough varchar or text field, and use implode() to flatten an array with choices. The advantage is that you can name your options ('cola|beer|water'), so you have a direct link between the stored data and your options, even if you reorder them or add new options. Downside is that you need more storage, and you have variable length fields, which is slightly slower performance wise.

Sometimes I use an integer, and bit operators. bit = 1, option is selected, bit =0, option not selected. This requires a fixed option order, as you need to know which bit belongs to which option. But it requires only a few bytes of storage, and creates fixed length records (unless you have other fields of course).

[eluser]mddd[/eluser]
If you don't need to search by these options, I totally agree with Wanwizard. Store them in some serialized way. Easy to display when you read the data and want to show it in some form or other.

If you do need to search for them, I would probably make a link table. If the values link to other tables (for instance: a company can be active in one or more countries, and you also store information about those countries), I would make a table with columns "company_id, country_id".
If you DON'T need to store information about the linked item (like cola, beer, water : you want to know which but they don't link anywhere) you could make a table with columns "person_id, drink" which could contain "123, 'beer'" and another record "123, 'water'".

If you have more of these fields / lists, you could keep them in a single table by adding an extra column:

Code:

person_id   kind   item
123         drink  beer
123         drink  water
123         food   pizza
123         food   salad

Then it is still easy to find people by their attributes: SELECT person_id FROM link_table WHERE kind='food' AND item='pizza'.

I realize that by storing values in bits, you can also still find them, but it would quickly get confusing especially if there are new items added to the lists etc.

[eluser]WanWizard[/eluser]
@mddd, very good point.

As said, using a bit pattern is only possible if the order of the options is not going to change. You can add extra options at the end of the list as long as you have bits left, but reordering them is going to give you a headache.

A search is possible on the imploded variant, but quite complex, you'll get queries like

Code:

// from an old chunk of ExiteCMS v7 code:
$result = dbquery("SELECT group_id, group_name FROM ".$db_prefix."user_groups WHERE group_groups REGEXP('^\\\.{$group_id}$|\\\.{$group_id}\\\.|\\\.{$group_id}$')");

which works, but might be quite slow as you won't be able to use indexes. Also, it's not portable.