Question 17914 in Laravel Question posted in
The official Laravel Documentation can be found here.

How much is too much when storing JSON in MySQL – Laravel

I have wondered about my current database design I made for my uni organization website.
In general this website is for displaying informations and events. The event is different for each departement. It does some normal things like displaying event, creating event for admin, event registration for user, etc. The problem is every departement may have different form for registering new participant, say departement A has 5 form input, but departement B has 8 form input. And yet, every event may have different total of form with multiple type like text, radio or checkbox.

To solve the problem I mentioned before, I designed the database that has events, departements, event_forms, form_types, event_form_options (for checkbox or radio when admin decided to choose that type of form), event_form_responses, and some other tables that is out of the context of this question. The thought process I had, that admin can create whatever amount of form input they want, and each form input equals one row of event_forms table that refers to certain event with event_id. But the "problem" that might occurs with this approach lies in event_form_responses table. In that table, user will have response for each form input of certain event. Say, event A has 10 form inputs, and then 60 people decided to register to that event, that means event_form_response will have 600 rows of response for that event alone!. And then it needs to be displayed in admin dashboard.

My question:

Will that impact query and website performance? I think it will. What if I change the event_form and event_form_responses to store it as a JSON instead? Is it more advantageous at this point?. So that every event_form can have different signature with different amount of form inputs easily. And for the event_form_responses will have 1 * U instead of Q * U, which U is total of user registered in said event, and Q is amount of form inputs. Thank you in advance

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After reading @Bill Karwin slide presentation, experimenting on my own and a little bit of experience from part time job, I conclude that I'm going to use json for both table with the following reason:

    • For the even_forms table, I realized that user will need update the data. So making the normalized version would be hard/complex because we don't know where the column user might change, or maybe at the same time, user want to add or delete the column at the same time with random order. So json is more managable and easier.
    • For the event_form_responses, my senior said that we need to limit our data fetching from database to keep website's performance. Usually it would be around 50 or so. If I was going to make it normalized, fetching 10 user form responses is equal to fetching 100 data (if the event form has 10 questions). So displaying 5 event form response at a time is not really good for user experience, is it?. But even though, the user for this website is not going to reach like 10K at a time or something (I think 300 - 1000 at most), I'm just going to consider that as a best practice

  2. 0

    I wouldn’t worry about 600 rows per event. Assuming you have designed indexes well to support your queries, MySQL can handle hundreds of millions of rows per table. Tables typically start to get hard to scale when you have over 1 billion (1e9) rows.

    I’ve answered a lot of questions about MySQL and JSON on Stack Overflow. My conclusion is that while JSON makes it easy to store data with variable or complex structure, it comes at a cost.

    Queries against JSON data are more complex than traditional SQL queries against normal rows and columns. It’s harder to learn to search or sort data stored in JSON. Not impossible — but it’s a totally different type of query. If you don’t have experience with this yet, you will experience a steep learning curve.

    You said you’re concerned about performance, and I have found that it’s harder to optimize queries that search or sort JSON than queries that work with normal tables.

    Also it depends a lot on how you structure your JSON. JSON is very free-form, you can make arrays and key/value objects and you can nest further structure. But in some cases, I’ve seen people create JSON structure that cannot be queried using MySQL’s JSON functions. You need to do a lot of study of the types of JSON functions available, and do a lot of hands-on experimentation to understand their strengths and weaknesses.

    Also I have found that it typically takes 2-3 times as much space to store data in JSON format compared to storing equivalent data in normal rows and columns. The reason is that numbers are stored as strings, object keys appear on every row instead of just in the table header, and there are extra characters needed for quotes, commas, and brackets.

    You might like my presentation How to Use JSON in MySQL Wrong.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search