1

I'm trying to create a basic form generator. So a user can click together an HTML form in some sort of CMS system. I'm still going over the database design for this, and i have a question when it comes to checkboxes.

My database design looks like this:

fields

pk_field_id | field_type | field_name   | default_value
1           | textfield  | text1        | Some default value
2           | textfield  | text2        | NULL
3           | radio      | radio1       | NULL
4           | checkbox   | check1       | NULL

So that's basically how i save the selected fields. When i read this table i can go through a switch statement and read the field_type value, so i know what kind of field to render.

But that's not all. I also have a table with the "values" that grouped elements like a radio buttons can have.

fieldvalues:

fk_field_id | label    | value
3           | Radio 1  | radio1
3           | Radio 2  | radio2
4           | Check 1  | check1
4           | Check 2  | check2

So now when i read the table fields i can JOIN the table fieldvalues and that way i know what options belong to the radio and what to the checkbox. This all seems to be working fine.

Now the tricky part. When the form is generated, i also need a place to save the entered values. So my Form Engine automatically creates an extra table for that. It creates a column for each field_name in the fields table. So in this case the dynamically created table looks like this:

myform

pk_form_id | text1 | text2 | radio1 | check1 | language_id

Now when a user enters data into the generated HTML form, i can easily save all the entered data. Because a textfield requires only one column. A grouped radio button eventually only submits one value. But a group of checkboxes can of course submit multiple values when two or more checkboxes are checked.

Now my problem is, how can i save that to the database? I don't think it's a good idea to store multiple values in one column, separated by a certain character. Because that also makes it difficult to perform a search on.

I initially thought this was a good way because i can easily pull a row from the myform table with a simple SELECT ... WHERE query.

So anyway, i'm not sure if this is a good setup... And how do i handle the multiple values that checkboxes can submit?

Any ideas about this?

Vivendi
  • 20,047
  • 25
  • 121
  • 196

2 Answers2

1

Storing the values as XML/JSON would be the way to go. Depending on your RDBMS, the db could natively handle the XML and/or you can query the doc using XPath. Drupal makes heavy use of this type of storage and it works fine for them.

kolossus
  • 20,559
  • 3
  • 52
  • 104
0

I think your issue is that you're not thinking of the check controls the right way. You need to think of each radio button or check box as its own control.

The fact that radio buttons are grouped together is a facet of radio buttons that affects their behaviour. Each radio button in fact has a value (checked=true,false). Your control value table should contain an entry for each radio button (and check box) just like it does for each text box.

Instead of keeping a child table for option buttons and their values, this information should be in your main control table which should be expanded to provide some mechanism for grouping these controls. You could have a control_group table with an optional foreign key from fields or you might just denormalize and have an optional group-name column on your fields table.

Joel Brown
  • 14,123
  • 4
  • 52
  • 64