Storing JSON as data values in a database

[carlosmoreeira]

This is more of a database architect question.

 

I am building a CMS for website development. I am ready to use it for the first time on one of my clients. Now, my issue is is how to set up some or one of my table(s) for some areas that my client wants to be able to change in the admin section.

 

In their website on the front page, I put together this nice block slider. Its 6 blocks with text, a background image and nice effects. At first it was always going to be static, which was no issue at all, but later they came to me and asked if they could be able to change words in the boxes themselves in the admin section..

 

Of course this can be easily done by creating a table that is associated with this home page block thing and each row in the table will correspond to one of the blocks on the home page.

 

But I believe there might be an easier way to store this type of data without having to create another table for every feature the client wants to edit, for example they also want to be able to edit a slider on another page.

 

The WordPress database then came into my mind and how they store most of their data as JSON. So I was then thinking of creating a table named 'modules' for examples with three columns (id, title, data). Then inside the data column store the editable fields as a JSON string object.

 

For example a row could like like :

 

1, "Front Page Box Slider", {"slider-home" : [ {"data" : "value", "img_1", "value1"}, {"data" : "value", "img_2", "value2"} ]}

 

I've been doing a bit of research on this and people have just been saying, never do this if you do want your data to be searched through, which I don't.

 

Can anyone tell me why or why not I should do this, or maybe a better solution to my problem?

 

Thank you

Edited September 18, 2014 by carlosmoreeira

[Jacques1]

First of all, Wordpress is not exactly known for quality, so I wouldn't use their stuff for inspiration.

 

The problem with storing JSON data (or any other violation of the First normal form) is that you work against the database system. You essentially break all features of MySQL and reduce it to a primitive string store. There's no type safety, no referential integrity, no way to actually query the data. You just have one big pile of characters that is meaningless for MySQL.

 

This also means you lose MySQL's ability to handle concurrent queries. Imagine the following scenario: Two PHP processes try to edit the same JSON document at the same time. The first process makes its changes and writes the result back to the database. Then the second process changes the original data and also writes the result to the database. Unless you have a special locking mechanism in your application, you've now overwritten all changes of the first process. That's obviously a problem.

 

However, the biggest argument against breaking the relational model is that it's usually done for the wrong reasons. You somehow assume that you need one table per slider, but I don't see why that would be the case. If you post your concrete data model, I'm pretty sure we can figure out a proper relational implementation.

[carlosmoreeira]

I never even thought of how storing this data goes against normalization, not even sure what I was thinking then. I guess that is why all WordPress databases I have ever worked with give so many problems. 

 

But then back to the other issue on hand, exactly how I should create my tables. I still dont have a solid data model for this problem. I use sliders as an example, but it can be a variety of other things as well. 

 

But this is the situation I am trying to figure out. Again I will use sliders as an example. 

 

The home page slider has 3 things that can be edited per each slide (img, text, box_legnth), there can be 'X' many slides. Another slider on another page has 4 editable things. (img, text, hovertext, fadetext), it can also have 'X' many slides. Since each slider does not have the same 'Attributes', I can't seem to figure out how to make these tables. 

 

I know I can easily solve the problem with 1 table per every slide (module), but that doesn't make me to happy. I believe there is a more clean and concise way of storing all this data and still follow the rules of normalization. 

 

In my query all I want to be able to do is say "Get me everything for this 'Module'". 

 

Again any help is greatly appreciated. 

[Jacques1]

If you actually have several entirely different slider models, there's nothing wrong with making different tables. Your data simply requires this. You can put common attributes (like the image and the text) into a separate table to avoid repeating them in all tables.

 

There's also the entity-attribute-value model. It's complex and ugly, but compared to the JSON massacre, it may still be the better choice.