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.