What's the best way of putting form data into a database when there's an unknown anmount of data?

Question

I have a pretty large form that consists of radio buttons/checkboxes as well as text inputs. Due to the nature of checkboxes, if they post the form without checking it, it isn't sent in the POST data. Which leaves me a bit stuck with how to handle it.

I originally started my database with the standard 'column for each field'. For example:

id | userid | firstname | lastname | middlename | phonenumber | mobilenumber |

That quickly changed when I ended up having upwards of 30 columns. This form is huge. I've decided I'll use an 'EAV schema' to give my table only 4 rows. It now looks like so:

id | userid | name      | value
---+--------+-----------+------
 1 |    1   | firstname | steve
---+--------+-----------+------
 2 |    1   | lastname  | blah

This seems like a nicer approach.

So my question is, how should I handle the database when I'm not entirely sure what's going in it? If I have 20 checkboxes (each with their own name), should I manually check if each was submitted and set a 'null' value if not?

Should I empty all the rows from the table for the user's ID and replace it with all the new data?

What's an efficient way of doing this?

Answer 1

EAV is an anti-pattern in this case. You will end up with very convoluted logic just to retrieve a single set of data.

Your first approach is more maintainable and understandable to others.

When it comes to a boolean value, such as a checkbox value, I would use a bit/boolean field in the database, where a check mark would be a true and the fact that you didn't get it posted back would become a false.

The same thing stands for the EAV schema - keep them all in the DB, just mark the value as true or false, depending on what was posted.

Answer 2

You should parse your input first, and determine proper values for each item. Then you take the data and put in the database.

The code should not rely on the existance of fields in the form to determine what fields to put in the database. A form can easily be manipulated, so it could be used to change any field in the table, not just the ones corresponding to the fields that you put in the form.

The EAV schema is good if you have plenty of similar fields, or a dynamic set of fields. Perhaps you should store the text data in the regular way and the values from the checkboxes in a separate table.

Answer 3

Perhaps a little off topic, but to work around the checkbox functionality, add a hidden input with the same name before the checkbox and value - zero (or a value that means 'not set'):

<input type="hidden" name="blah" value="0">
<input type="checkbox" name="blah" value="1">

in that way, if the checkbox isn't checked, you'll still get a value of 0 in your post - but if it is checked, you will get 1, because the last field of the same name is POST'ed.