Store inputs in JSON or one column per input?

[DeX]

I have a system where users enter about 100 inputs, not all are required. We capture every input when the user clicks save and they have the ability to look back at any save they have and edit the inputs to re-save.

Currently we serialize the inputs, wrap it up in a JSON string and save it into a column in the MySQL table. I'm wondering if there would be a speed / storage advantage to specifying a column for each input and storing each value in its own column.

If  you're wondering how often the inputs change, it's infrequent. We might add / remove / modify about 20 inputs a year, probably fewer. If you're wondering how often saves are done, it's about 8 users doing 40 saves each, per day.

[Barand]

So the purpose of this hoard of data is to keep someone busy; input it - store it - forget it?

(Sounds like the IT equivalent of repeatedly rolling boulders up a hill then letting them roll down again.)

If that is all it is, and you have no further use for the data, then JSON format sounds fine.

[DeX]

The inputs are related to quotes being sent to customers so often times a customer will want to modify their quote, in which case the user will open an old one, load all of the input values from the database and change some, only to re-save it with the new inputs. The re-save does not modify the old table row, it simply creates a new row under the same account.

[Barand]

So analysis or breakdown of the data will never be required?

[DeX]

Sorry, I should have included that. We will be running reports based on which quotes have certain criteria:

- how many are over a certain length

- how many have item X selected

- how many of item X we sold

These are all based on user entered inputs and wrapped up in the JSON. The reports are generated whenever anyone loads the reports page to view them, they're not done on a schedule like nightly.

[Barand]

Then you need to decide wihich items of data are required to get the analyses required and separate them out into processable columns.

[gizmola]

Given how you are doing this, a big win would be to ditch MySQL and use a no SQL database like MongoDB which is schemaless and works with a binary json format internally.