organisation of table for dropdown boxes

Question

Hi, I am using CakePhp and MYsql for my application

 I am having three tables 
 Forms(id,name,created,modified)
 Attributes(id,form_id,label,type,sequenceno)
 Results(id,form_id,attribute_id,label,value,submitted_by)

The application i am developing is like form Builder. Generating the fields based on the type (eg .. If type="text" ->generate textbox)

Now i am having a doubt regarding if i my type is "Dropdown" then it will have a dropdown box with some 3 options.. How can i keep those choice name.. Eg.. Male Female

My sample entry in the table will be like in attributes table

       1 1 name text 1
       2 1 age number 2
       3 1 address Textarea 3

And in my results table

       1 1 1 name aruna 
       2 1 2 age 22
       3 1 3 address XYZ

that is how can i save male and female choices in the table..SInce i will have the privilage to change the choice name even in between ... Please suggest me

Answer 1

Did you try using an enum?

The ENUM Type

Answer 2

If I understand well you want to store the possbile answers for dropdown-type attributes? Then you have two approches:

• Separate table, holding parent attribute's id and possible value (one per row)
• Field in the attribute table, contaning possible answers as label/value pairs. In this case, you can store in a TEXT column a serialize()'d PHP associative array containing value => label. It is easier for maintainance and usage in PHP (unserialize the field to use it, change it, single UPDATE to modify), but you won't be able to is this value properly in a SQL query.

Edit: example for solution #2

Add field answers of type TEXT to your Attributes table. Then run this PHP code:

// obtain this array programatically:
$genders = array(
    ''     =>    'Unknown',
    'M'    =>    'Male',
    'F'    =>    'Female',
);
// put this into the 'answers' field instead of displaying it
echo serialize( $genders );

This will give you an attribute entry like this (which explains why this is hardly query-able):

   4 1 gender dropdown 4 a:3:{s:0:"";s:7:"Unknown";s:1:"M";s:4:"Male";s:1:"F";s:6:"Female";}

then in your PHP code, just:

$genders = unserialize( $my_attribute_row['answers'] );
// here you have your array back