Jump to content

Store inputs in JSON or one column per input?


DeX

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.