Storing Tick Boxes in mySQL

Best Practice

[ukgimp]

I am trying to work somethng out.

For each record there is a number of tick boxes which may or may not be ticked.

Clearly a flat file would be easiest with a table of 1/0 but that seems like a bit of a waste of space. How would you store such info

MAIN
id {PK}
otherFeilds

TICKBOXES
id {FK}
tickBoxId (FK}

TICKBOXTYPES
tickBoxId {PK}
type

Then group by each main ID and get the corresponding tick box types.

Bearing in mind that there may be up to 50 tick boxes per record and up to 30K records

Is this the best way? Or is there an alternative

Cheers

[dmorison]

One alternative is to store a PHP serialized array of "true" tickbox values in a single varchar(255) or BLOB (depending upon size) field.

This puts the onus on your application to know that if nothing is stored in the DB then the checkbox value is "off" (which is logical, and space saving :)

Assuming that your checkboxes each have a name that can be used as the key to an associative array (this may be what you mean by "type" in your post); then you can just build an associative array of true values.

For example, let's say you were processing a form submission where each of your tickboxes had a name consisting of box_[type], where [type] is of course different for each checkbox.

$tickboxvalues = array(); 
foreach($HTTP_POST_VARS as $k => $v) 
{ 
 if (substr($k,0,4)=="box_") 
 { 
 if ($v=="on") 
 { 
 $v = substr($v,4); 
 $tickboxvalues[$v] = 1; 
 } 
 } 
} 
$store_in_db = serialize($tickboxvalues); 

Be aware that this of course binds your data model to PHP which some people will lose sleep over, but it is an option.