如何构建MySQL数据库用于存储多个复选框表单数据，然后在php中进行统计？

Question

I am working on a form for storing information about the themes of the queries we receive in our company.

I am using php/MySQL to store radio button data such as:

• Name of employee
• Medium of query (e-mail/phone/in person)

With radio button data such as this, I can easily use PHP to query the database and e.g. count the numbers of queries answered by e-mail by each employee.

The trouble I am having is with data regarding the theme of the query. This is checkbox data with a lot of different checkboxes (50+). We would like to be able to add or remove checkboxes from time to time, though not very often.

I used to store this data just as comma-separated values in a single cell in the database and then export to excel to work with the data, but now I'd like to use another PHP form to generate statistics on the themes.

My research has lead me two to ways of doing this, that may be possible:

1. Creating a separate table in my database for my themes with one column for each possible theme, so that I'd have as many columns in my database as the number of checkboxes in my form.
2. Use the php functions serialize to store the checkbox data in one cell in my database and then later using unserialize to work with the data in php.

I am an absolute beginner, so with both options I'm unsure how I'd actually implement it:

1. With this option I am unsure whether my MySQL columns should just be "theme 1", "theme 2", etc., or whether they should have the names of my checkbox values, e.g. "money", "personal problems", "practical issues", etc. I have not been able to find a good resource on how to store the checkbox data in the right way, when the user may sometimes have clicked just 1 theme, and in other instances may have clicked 10 themes.
2. With this option I am unsure how I could populate a dropdown with unique values, and how I could later count instances of a unique value across the rows in my database.

Any help you can give me on this, including links to tutorials or questions similar to this on stackoverflow, would be much appreciated. I haven't been able to find anything about this that I could understand, other than I am thinking option 1 is probably the right way to go.

EDIT: After having received an idea about how to do this from David, I am updating my post with my attempt to understand how I would go about this.

Answer 1

What you're describing can be thought of as a many-to-many relationship. You have:

• A form record, which can relate to many themes
• A theme, which can relate to many form records

In a situation like this, the relationship itself is a database record. Consider this table structure:

FormRecords
----------
ID
SomeTitle
UserIDWhoFilledOutForm
etc.

Themes
----------
ID
ThemeName
etc.

FormRecordThemes
----------
FormRecordID
ThemeID

Each "primary entity" has an identifier and information about that entity. Then there's a "linking table" which has information about the relationship between those two entities.

Any time you present a form, you simply select from the Themes to populate the check boxes. You can add new ones as you see fit. You probably shouldn't remove any, though you can "soft delete" them by setting some flag on the record to indicate not to display them on the form.

If you ever want to edit the Themes in any significant way (a way which would somehow invalidate previous uses of that record, such as completely changing its name/title), then keep in mind that you'd be modifying the entire history of its use. I don't know if this is a risk in your domain, but in cases like that it might help to de-normalize a little bit by storing "Theme at that time" values in the relationship table. Like, the name of the Theme at the time that relationship was created. It's best to avoid this scenario entirely if possible, mostly by making key Theme values immutable in the domain.

---

Don't store delimited lists, don't store serialized data (unless the entire object really is a single data point)... Keep values separated into their own actual values in the database. Relational databases are really good at querying relational data.