There is the following data structure

Школа - Количество учеников - Класс - Количество учеников в классе Школа - Количество учеников - Класс - Количество учеников в классе - Класс - Количество учеников в классе

At the same time, in the process of using the database, it is necessary to do sampling by the value of "Number of students", and sampling by classes is not required, moreover, data on classes is always used for each of the schools as a whole. I.e

 Школа - Количество учеников // ****************************** // Начало единой структуры данных - Класс - Количество учеников в классе - Класс - Количество учеников в классе - Класс - Количество учеников в классе // Конец единой структуры данных // ******************************

There are two possible options:

  1. Create a separate table for classes.
  2. Create a single field for classes and put data there in a special format, for example, JSON.

In the second case, the base ceases to be relational in its pure form, but the schema is simplified and data access is accelerated (less SQL is needed).

What are the pros and cons of the two approaches possible?

That is, it was

 таблица Школы таблица Классы

It became

 таблица Школы

Where there is a Classes column containing JSON, which for each school now stores the data that was previously in the Classes table.

  • read about normal forms of relational db. bringing the table to a form (which one I do not remember exactly) implies that it does not contain data that can be calculated. in your example, this means the following: that there is no sense in the school table to keep the number of students, since this information is easily calculated from the table. If your database contains information about students, then there is no point in storing the number of students in the class table. as this information can also be calculated. if you can not independently design a database, ask the question “how to design a database” + list of requirements for it - jmu

2 answers 2

The question is not quite clear, more precisely it is not completely clear what needs to be obtained at the output. What is not satisfied with the structure

 | school | class | amount |

where, school is the school number, class is the class, amount is the number of students in a class.

To select the total number of students in the context of schools we will use the query:

  SELECT school, SUM(amount) FROM tablename GROUP BY school

To select the number of school students by class, query:

  SELECT class, amount FROM tablename WHERE school=some_value
  • The idea boils down to the rejection of a rigid database schema. That is, instead of a table associated with a set of other tables with foreign keys, we get one table. At the same time, we lose the opportunity to use SQL when searching in parts of a document (it becomes a single whole), but we gain flexibility and simplicity (the document structure can be changed without changing the database schema, and the tables become smaller), moreover, the documents themselves can be taken out of relational base and transfer to the key-value base. - stanislav
  • It all depends on the set of operations that you will need to perform. For example, count the number of students in a school, change the number of students in a class, increment a class (transfer all students to the next class) ... In the approach with a text field in which to store information as JSON (or in any other form) this task doesn't seem so trivial. Requests will be incomprehensible, and the code is difficult to understand. - Vitalii Maslianok
  • Although, again, everything is relative. Everyone has their own style. But having in my hands such a powerful weapon as mysql, I think you need to use its capabilities to the full - Vitalii Maslianok

The task, since it is written to you - SQL does not require at all. You can create a separate file for each class and store a JSON structure in it — dry and comfortable.

You still have to unpack JSON for any operation, change and pack it back. That you will read / write a database, that the file is the same (although it may be a couple of percent quicker on files).

If you do exactly the database, then you should have a structure like:

 Школы -> Классы -> Ученики.

In the "classes" table, the reference field to "school", in the "students" table - a link to "class".

All manipulations are simple and transparent. The most complex query contains two join. Whether it will work faster than JSON is unambiguous, especially on the tasks "to count all students of the school", "to find out how many Ivanovs are in our school", "to transfer Vasya from 5a to 5b".

You can write "on the topic" with folios - which is not clear or interesting - write questions.

  • If there is only one school, the "Schools" table may be dropped. - SilverIce