2

I'm using laravel and I have currently more than 1 json column in a table. Should I put all json stuff into one column or is it ok performance wise to use multiple columns for json stuff?

For example, I have these two:

        $table->json('seo')->nullable();
        $table->json('settings')->nullable();

Or would it be better to have one json column named meta and there store settings and seo?

        $table->json('meta')->nullable();

Any recommendations? And if you have give me some explanations of why or why not.

niko craft
  • 2,393
  • 1
  • 28
  • 48
  • 1
    For efficiency of searching and returning results in a large database, by having several different columns to refine your search will improve the time of returning search results. – party-ring Apr 25 '19 at 15:04
  • if we exclude searching of these columns, they will only be read from, are there penalties for having 2-3 json columns instead of grouping them in just one column called meta? – niko craft Apr 25 '19 at 15:08

1 Answers1

2

There are no penalties for having multiple JSON columns. No more than having multiple BLOB columns. I mean, it takes just a few more bytes per row. But that's probably a trivial difference relative to the size of the JSON data. I wouldn't worry about it.

What you should think about, however, is defining some kind of rules of what fields you are going to store in each JSON document. What counts as seo versus settings? If you had to pass this coding project to another programmer, would they know how to use these two columns?

Bill Karwin
  • 462,430
  • 80
  • 609
  • 762
  • great feedback and great questions Bill, I'm making a CMS, the table in question is posts/pages table, seo is json column that would store seo related data and settings would store some settings for post or page, these settings would be pretty simple and they are just there so each post or page can override some global settings for posts, pages types. Thanks for your feedback! – niko craft Apr 25 '19 at 15:29
  • If you got some time on your hands, I'm making a modern CMS based on php and javascript, see it here: https://www.youtube.com/watch?v=pCjZpwH88Z0 You just helped me greatly with your expertise, I've checked your profile, thanks for the great work you do here! Much obliged! :) – niko craft Apr 25 '19 at 15:34
  • 1
    Congratulations on your project! That's a ton of work. – Bill Karwin Apr 25 '19 at 16:00
  • yeah a ton, 2 years in dev already, hope to release it at the end of May :) – niko craft Apr 25 '19 at 16:02
  • Bill, I have another question I would like to ask you, should I post it here on stackoverflow or could I ask you somewhere more directly? I'd just like your feedback on something, it's properties table pattern that is connected to the main table via relationship (using laravel here) vs using json in a column for properties and skipping properties table pattern entirely. – niko craft Apr 25 '19 at 16:09
  • So far I've used properties table since it allows me just to easily add all kind of properties to posts/pages, but now I'm in favor of using json column to storing all this data since json columns are very flexible, are compressed when saved and I can shave of a table or two and shave off relationship or two since json column is directly accessible. I see the exensive epxerince you have, I'd like your thouths on this. Let me know if I should post another question or if I could get in chat with you for 4-5 minutes on this :) Thanks greatly! – niko craft Apr 25 '19 at 16:09
  • 1
    Post a question, I may post an answer if I have time, but I'm working too. :) Short answer is: It depends how you need to query the data. In the past, I've suggested using JSON as an alternative to EAV (the properties table you are probably talking about). But I've seen some pretty awful uses of JSON in other questions here, so I'm less enthusiastic about JSON. Read some of my past answers on JSON: https://stackoverflow.com/search?tab=newest&q=user%3a20860%20%5bmysql%5d%20%5bjson%5d – Bill Karwin Apr 25 '19 at 16:46
  • I posted the question here with a good explanation about my situation, I do not need ever to query posts based on properties, give me your opinion :) I will go with json, but I want to hear from experienced dev like you https://stackoverflow.com/questions/55854169/properties-table-pattern-vs-storing-all-properties-in-json-column – niko craft Apr 25 '19 at 16:49