How normal should my checkbox data be for storage in database

Question

I have a web form with about 15 checkboxes that users may check 0 or all 15 checkboxes and any quantity in between. The database that will store the form data is MySQL, but reports will be generated from the data in MS Access using a MySQL ODBC connection. I see three options for handling this.

The Spreadsheet way:

Have one table with a boolean column for every checkbox and a text box for "Other"

The Normalized way:

Have two tables, one with the form data other then the checkbox info. Then a second table containing the FK of the form data and the value of the checkbox in a one to many relationship. Handle the inserting of the checkbox values separate from the insertion of the form data with some looping when the form is processed.

The Short way:

Have one table with a text field for the checkbox data. When processing the form concatenate checkbox values into a comma separated string and put that in the text field along with the other data.

---

Both the Access way and the Short way are enticing because of the ease in which they can be used to generate reports, the Short way especially. Unfortunately, while I prefer the normalized way no one in my organization who develops the Access parts knows how to generate reports that use the normalized data properly, at least not cleanly. Both The Access way and the Short way can be problematic when filtering by checkbox values (the Access way more so).

How should I proceed in this? If I go the normalized way I will also have to take responsibility for developing a tool to generate reports, which may step on a few toes and be a bit of a political wrangle. I stick with what they are using already (The Access way) increasing my immediate workload and buying myself a mess of programming problems down the line, but avoiding any office politics. Or the hybrid way which costs a bit of programming now and some annoyance later, has only a few obstacles in other project members?

Answer 1

I disagree that what you call "the Access way" is not normalized. As long as all the checkboxes have a different meaning, and are dependent only on the key (and not on each other), the table is normalized (at least, 3NF or BCNF).

In other words, if you don't see any trouble reporting with that structure, go ahead, Codd won't come and haunt you in your sleep. (And even Date will probably be ok with this, as long as you don't store "off" checkboxes as NULL :p)

Answer 2

If the true/false value are discrete pieces of data that are directly related to the PK then you could put them in the same table as the entity they are for.

If you wanted to separate it out into another table then that is fine. Just put a column for ever option. This is easy to expand or remove in the future by adding or removing columns.

I would not recommend the list of comma separated value because that is not as maintainable and leads to confusion.

Answer 3

Design your data to be captured in normalized form.

In your Access DB, build a Crosstab query that will display it in the format you described, with a separate column for each item of checkbox data. Use that crosstab query as the view from which people extracting data for reports make their selections.

You get the best of both worlds, at the expense of spending some time performing the crosstab query. If this delay gets to be exhorbitant, consider snapshots.