Jump to content
DeX

Store inputs in JSON or one column per input?

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.

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.