Checkbox Multiple Values

Best way with MySQL?

[rover]

If I have a field that will use checkboxes on a php/html page that would have multiple values from a set list of options, should I be using a mysql field type like set or enum?

Or should I not try to capture this all in one field, and rather be using three tables (main table, attribute table, and a table that maps the relations between the main table and attribute table)?

I need users to be able to add the options to their profile, through checkboxes, and I would need to have it searchable by the attributes.

So if the field was colors, and the user would choose from red, blue, green (and they can choose more than one). Later they could log in and make changes to their selections when updating their profile.

(In the actual application there would be about 30 - 40 choices not just 3).

I think initially it might be easiest for me to use a set or enum field, but I don't know if this would be the right way to do it, or if I should be using the separate tables.

If anyone has any insight about this, I would appreciate it very much...

[physics]

Why not just make a table like this

id color
1 red
2 green
3 blue
...

And draw the id, color from there to use in the checkboxes. Then in their profile store just store the id. Or is this what you were saying already above?

[aspdaddy]

The normalised way is to have a 3 tables as you suggest.

This can be a bit of extra work writing queries if your datbase doesnt support Views, but it is the best approach.

An alternative to a SET or Enum type is to treat the textboxes as an ordered list of binary flags and calculate and store a single number that when converted to binary shows you which boxes are ticked:

Red, Blue, Green
1=Green
2=Blue
3=Blue,Green
4=Red
...
7=Red,Blue,Green

You can either solve it in the application or database (prefered).

HTH

[rover]

Thanks very much for the input. I think I will spend the extra time initially (for setting up the queries, etc.) to create this in a more normalized way, using the tables to hold the values.