It is recommended to use the JSON type in the columns in the table below

Question

I am learning relational databases, so I decided to create a school administration system, I made a table only to keep all the student’s school history (all the newsletters with other information). In the table, each column of a newsletter is of the type JSON, these files will have a predefined format, I thought it would be a good use JSON but now I was left with a foot behind.
Finally, it is recommended IN MY CASE type JSON in the columns?

[Below is a part of the table as I imagine in Mysql]

'''

CREATE TABLE historico_esc_aluno (

        boletim1SerieF1 JSON NOT NULL,
        boletim2SerieF1 JSON NOT NULL,
        boletim3SerieF1 JSON NOT NULL,
        boletim4SerieF1 JSON NOT NULL,
        boletim5SerieF2 JSON NOT NULL,
        boletim6SerieF2 JSON NOT NULL,
        boletim7SerieF2 JSON NOT NULL,
        boletim8SerieF2 JSON NOT NULL,
        boletim1SerieM1 JSON NOT NULL,
        boletim2SerieM2 JSON NOT NULL,
        boletim3SerieM3 JSON NOT NULL,

) DEFAULT charset = utf8;

'''

I thought about using Mongodb (to do the history only), but I saw a video that said it is not the best practice when you want to save information in the long term (data that cannot be lost).

Answer 1

I’m learning relational databases, so I decided to create a school administration system

If you are learning relational databases, you should set aside the type of JSON data and try to understand conceptual modeling and data normalization with the most common types, so you can make the decision to use JSON or not in this specific scenario.

There are no problems in the JSON type, there are cases where it may be useful in a relational database, but when using this type of bank you are usually looking for the ACID, an acronym to indicate Totomicity, Consistance, Isolace and Durability.

Because JSON is a document, it is difficult to ensure its consistency, constraints similar to the behavior of foreign keys are more complex to be created. Querying specific values in this column can become more difficult, and you will need to use specific DBMS functions to manipulate the information. In addition, you will need to understand the difference in performance of the operations you will most utilize to know if it is feasible for your project.

Anyway, it is recommended IN MY CASE to use the JSON type in the columns?

There is no benefit to using a relational database to have a JSON-only table, especially when your data has potential for query, manipulation, and report building. You must understand its structure and normalize it to meet your needs.

I thought of using Mongodb (to do the history only), but I saw one video that said it’s not the best practice when you want to save long-term information (data that cannot be lost).

You need to understand the reasons when someone says it’s not best practice (or it’s best practice).

While relational databases ensure the ACID, a non-relational database can give up one of these pillars to ensure some other benefit, this usually being higher speed, scalability, aggregation support, etc. Mongodb for example, until some time ago had no recourse to transactions, something that in relational database is used to ensure atomicity (all or none of the operations will be performed).

Your data will not be lost using Mongodb, but if a non-relational database is ideal for your scenario is a decision you need to make considering the characteristics of the two technologies.

Documentation of the JSON data type in MYSQL, talking about normalization and its manipulations.