best way to save multiple checkboxes value in a database

[raamay]

hey experts, please advise me what is the best way to save multiple checkboxes value in a database. I have 6 checkboxes and i came across storing the values in a single column of a table which i dont want to implement as the checkbox values may be used for search purpose in latter stage. Apart from that i also came across creating individual columns for each checkbox which i feel would really make my table large. Is there any other way that i can do the same in a professional way?

[Atli]

Hi.

That would depend on your data. But you are quite right not to want to store them all in a single column. That would violate the first normal form.

If the number of checkboxes is not going to change, and each row of the table should only contain a single value for each of them, the most efficient method would probably be to simply add a column for each of them.

+-----+-------+-------+-------+
| ID  | Box1  | Box2  | Box3  |
+-----+-------+-------+-------+
| 1   | TRUE  | FALSE | NULL  |
| 2   | TRUE  | NULL  | FALSE |
| 3   | NULL  | TRUE  | FALSE |
+-----+-------+-------+-------+

(If you feel this would clutter your original table, you could also simply put these columns in a separate table and set up a 1:N relationship between them)

But, if the checkboxes are dynamic, and you want to be able to add/remove boxes from the list, you may want to set up a N:M relationship, where each row in your table could have a value for each checkbox.
This would require a separate table to store the ceckboxes, and an intermediary table to store the values each row in table A has for each checkbox.
Like:

/* Your table */
+-----+
| AID | 
+-----+ 
| 1   |
| 2   |
| 3   | 
+-----+

/* The table for the boxes */
+-------+-------+
| BoxID | Label |
+-------+-------+
| 1     | Box1  |
| 2     | Box2  |
| 3     | Box3  |
+-------+-------+

/* The intermediary table 
 * linking them together */
+-----+-------+-------+
| AID | BoxID | Value |
+-----+-------+-------+
| 1   | 1     | TRUE  |
| 1   | 2     | FALSE |
| 2   | 1     | TRUE  |
| 2   | 3     | FALSE |
| 3   | 2     | TRUE  |
| 3   | 3     | FALSE |
+-----+-------+-------+

See what I mean?

[raamay]

Thanks man

i m sure the 2nd option is gonna work for me because my checkboxes is not dynamic. Anyway thank you man!

[Juan Julian]

Greatest response ever, thank you Atli