Saving a JSON in Database vs Relationship?

Question

When I didn’t know much about the question of database structure and relationship between tables, I used to use JSON or comma-separated data in a given field of any table in the database.

In fact, I remember a specific case where I needed to relate several images to a particular post. These images I saved a list on JSON in a certain field called imagens.

The structure was more or less like this

-----------------------------------------------
tabela
-----------------------------------------------
id | imagens                      | usuario_id
-----------------------------------------------
1  | ["image1.png", "image2.png"] |   55
2  | NULL                         |   56

However, at a certain point where changes in this system began to appear, I was harshly criticized by a programmer who knew more about databases than I (and was also mocked) for having done so.

He explained to me that the data should look something like this:

--------------------------
tabela
--------------------------
id | usuario_id
--------------------------
1  |  55
2  |  56


----------------------------
imagens
----------------------------
post_id | url_imagem
 1      | image1.png
 1      | image2.png

Considering these two cases (VS serialization relationship saved in a field), we can state that, structurally speaking, using the JSON is a bad practice?

I was criticized for using data in JSON in a table field instead of a relationship, but there is some case where I should/can use a serialization and save it in a database field, without this being considered a bad programming practice?

Answer 1

A relational database, normalized, assumes that each attribute (value of a column in a table) is atomic, that is, it cannot be broken into smaller pieces from the point of view of the relational model. From the point of view of the relational model, It’s worth noting: no one would suggest that you store each digit of a number in a separate column, or each character of a string. If a data is not used to form relationships between tables, this data can be considered for all atomic effects.

In your example above this is not necessarily true: you have a list of images, okay, but can this list of images be considered atomic? Each image belongs to a single table row, there is no image overlay (i.e. the same image belongs to several posts distinct), you will always be interested in the full list and nothing else (i.e. it is not possible for you to solve, for example, just grab the first image from the list, to make a thumbnail or cover image for example)?

If the answer is a definite "yes", there is no chance that you will parts from this list of images to anything, or is the entire list or is nothing, so no, it is not bad practice to store the list this way, whether using JSON, comma-separated strings, Blobs, etc. In the same way that your image is in an external file, and that file is seen by the relational model as an atomic thing (you don’t reference "pieces" of that image in your model, only the entire image), the list of images - or even something more complex and structured - can rather be seen as an atomic entity, and therefore saved in a single attribute.

However, most of the time the answer is not a "yes": it is perfectly possible to conceive use cases where an image is shared by more than one post, or that you want to recover only a subset of post for anything, or even if you want to associate with each additional meta-data image (such as type, size, creation date, etc.). In that case, a representation normalized is usually ideal, and this alternative suggestion would correspond to a normal form.

As a rule, I would say to, in doubt, adopt the normalized form: it is easier to denormalize a table later (in case of performance problems, for example) than to normalize one that started denormalized. Unless you have good reasons to save a JSON in a table avoid doing this. There are cases where a JSON is the best representation for a data set, but in this case a non-relational database - commonly referred to as Nosql - may be a preferable option.