Search-friendly way to store checkbox values in MySQL?

Question

What is a search-friendly way to store checkbox values in the database?

Currently, checkboxes are processed as an array and values are separated by a ";"

As such:

<input type="checkbox" name="frequency[]" value="Daily"/> Daily
<input type="checkbox" name="frequency[]" value="Weekly"/> Weekly
<input type="checkbox" name="frequency[]" value="Monthly"/> Monthly

The PHP backend runs implode(';', $frequency) and adds the string to the database.

This works fine but it's a nightmare when it comes to searching.

Is there a better way to approach this?

Answer 1

You have two choices,

1. Use a second table to store the frequencies. Something like:

   CREATE TABLE `frequencies` (
       `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
       `order_id` INT NOT NULL ,
       `frequency` ENUM( 'Daily', 'Weekly', 'Monthly' ) NOT NULL
   )
   

2. Create one field for each frequency. I wouldn't recommend this approach.

Answer 2

As with so many other questions about relational databases, the best answer is normalization.

Multi-valued attributes need to store values on multiple rows:

CREATE TABLE Frequency (
  form_id INT NOT NULL,
  frequency VARCHAR(7) NOT NULL,
  PRIMARY KEY (form_id, frequency),
  FOREIGN KEY (form_id) REFERENCES FormSubmissions(form_id),
  FOREIGN KEY (frequency) REFERENCES FrequencyValues(frequency)
);

Answer 3

You can set type values in the database like this:

insert into myTable set frequency = 'weekly';
insert into myTable set frequency = 'daily,monthly';

select all rows that have "monthly"

select * from myTable where frequency like '%monthly%'

Be careful, by using set type fields, you don't have your database fully normalized. That doesn't need to be a problem per se (have a look at the tables that MySQL uses itself.. they're used all over). Still, make sure you know what you're doing.

As the MySQL documentation states:

The MySQL SET datatype is not the perfect solution for all MySQL databases, but can be quite powerful when appropriate. If you need to track less than 64 attributes for a given entity and make comparisons between different entities, the MySQL SET datatype may be ideal for your needs.

Answer 4

You could attribute powers of 2 to each value, and store their sum. Then from that sum, you can backtrack what was selected using a simple algorithm using the mod operator.

For example:

<input type="checkbox" name="frequency[]" value="2"/> Daily
<input type="checkbox" name="frequency[]" value="4"/> Weekly
<input type="checkbox" name="frequency[]" value="8"/> Monthly

• If your database stored 12, you know that it's Weekly & Monthly
• If your database stored 4, you know it's Weekly
• If your database stored 10, you know it's Daily & Monthly
• And so on.

This technique can be used to pack multiple information in one sum. It would be useful to save space and increase performance (dealing with integers instead of strings). On the other hand, it involves more development overhead.

Another quick and non-scalable way would be to simply have three distinct bit/boolean columns (1 or 0), but note that this wouldn't scale well if you were about to add new options.

Answer 5

This works fine but it's a nightmare when it comes to searching.

Other people have good answers here, but I'd like to suggest you approach the problem a little differently. How is it "working fine" if you can't search it? That's kind of like saying "writing to /dev/null is great, but reading is a problem".

I suggest you first think about how you are going to use the information (i.e. how it matters to the business), then think about what information to collect from the user, then think about how to store that so that it can serve the purpose. It's tempting to jump straight to step 3 because in most cases it's "obvious", but when you run into a not-so-obvious situation, go back to the beginning and the solution will be clear.

Preferably, think of at least a couple ways you might use the information. That helps avoid overly-specific data representations. Also avoid overzealous "let's collect this information to be on the safe side". That leads to lots of optional information with no clear connection to the business, which leads to misunderstandings about its meaning. When its time to query the information, you'll have a mess.

Answer 6

Assuming you use the 5 rated best answer on this, how would you then go about searching orders that have frequency monthly AND weekly? The only way I can think of at the moment is to join the same table up for each AND value.

What would be the correct way to get all orders that have a frequency of weekly AND monthly?