Designing a database structure for huge sets of form data

Question

I am converting a form that takes up 35 pages of A4 size to a web based version, and will need to retain the data for each form submission in a MySQL database.

The form is complex and we could be looking at over 500 individual fields for each form submission.

I've been reading about the EAV data approach, which seems like it might be a good option. The info I was reading for EAV however suggested it was only a good choice for sparse matrix data, which I imagine this will not be.

500 columns seems like bad design, what do you think?

Why 500 columns in a table seems like a bad design to you? Where's the threshold after which a wide table becomes an example of bad design? — mustaccio, Aug 30 '17 at 18:35
Is it like if I answer yes to question 4, then go on to question 5. But if I answer no, then skip the rest of this page and go on to the next page? — DCook, Aug 30 '17 at 19:30
@mustaccio I read this question and people seemed horrified that this guy had used so many columns — Marc, Aug 31 '17 at 14:06

score 1 · Accepted Answer · answered Aug 30 '17 at 19:03

1

Have 'real' columns for a few of the fields -- the ones that you are likely to search or filter on. Throw the rest (or, simply, all) of the fields into a big JSON string as another column. Perhaps MEDIUMTEXT.

answered Aug 30 '17 at 19:03

Rick James

78,038
5
47
113

And consider doing this in Postgres rather than MySQL to take advantage of its jsonb data type feature where the submitted JSON is parsed and then stored in an internal binary format that provides for indexing attributes in that JSON data. See this Question and see this wiki. – Basil Bourque Aug 30 '17 at 23:00
That's a really good idea, thanks Rick. I will probably end up using this idea with a small adjustment, in that I'll serialize the data using php, then save as something like MEDIUMBLOB. Thanks for an excellent suggestion. – Marc Aug 31 '17 at 14:08
1

@Marc - There are many serialization techniques; I recommend JSON because of all the tools available, its popularity, and the ease with which PHP handles it. If you use json_encode and need utf8, be sure to add JSON_UNESCAPED_UNICODE. More discussion . – Rick James Aug 31 '17 at 14:24

Designing a database structure for huge sets of form data

1 Answers1