1

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?

MDCCL
  • 8,150
  • 3
  • 28
  • 54
Marc
  • 113
  • 4
  • 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
  • Some government form? – Rick James Aug 30 '17 at 19:03
  • 1
    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
  • 1
    @mustaccio I read [this question](https://dba.stackexchange.com/questions/3972/too-many-columns-in-mysql) and people seemed horrified that this guy had used so many columns – Marc Aug 31 '17 at 14:06

1 Answers1

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.

Rick James
  • 66,863
  • 4
  • 38
  • 92
  • And consider doing this in Postgres rather than MySQL to take advantage of its [jsonb](https://www.postgresql.org/docs/current/static/datatype-json.html) 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](https://stackoverflow.com/q/22654170/642706) and see [this wiki](https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4#JSONB_Binary_JSON_storage). – 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_](http://mysql.rjweb.org/doc.php/eav) . – Rick James Aug 31 '17 at 14:24