How to implement dynamic fields and sections for a table?

Question

PostgreSQL

Is
table1, table2, tabl3....
it necessary to add dynamic fields to the tables

table1.name
table1.height
....
table2.name
table2.value
....

these fields are attached to sections
that are also dynamic
as a result for table1
table1.namethe section Info
table1.heightbelongs belong to the section Additional information
If you do on postgress:
table1, table2 ....
manually create where
table1
has for example
values ​​\u003d jsonb
and 2 more tables
Section - where the name of the section is stored for all tables
and Fields- store all fields for tables with FK on TableN and on Section
But I'm afraid to run into the possibilities of jsonb.
Perhaps it's time for mongo, but there is no experience and thoughts, there are only questions (which are also relevant for jsonb).
How to validate, how to remove fields, how good the search is.
Please share your thoughts on how best to solve the problem.

Answer 1

Your idea of ​​dynamic fields fits well with the concept of NoSQL.
MongoDB allows you to store documents in a single collection (table) in almost arbitrary JSON form.
In MongoDB, documents (records) look quite simple on the example of a product catalog:

[
  {
    _id: ObjectId("1"),
    "name": "Шкаф",
    "category": "Мебель",
    "price": 18000,
    "weight": 20.0,
    "details": {
      "height": 180,
      "width": 60,
      "depth": 40,
      "color": "белый"
    }
  },
  {
    _id: ObjectId("2"),
    "name": "Молоко",
    "category": "Продукты",
    "price": 100,
    "weight": 1.0,
    "details": {
      "volume": 1.0,
    }
  }
]

MongoDB does not have the usual fields and validation of structures, as in SQL. Those. The responsibility for data validation falls on the shoulders of the developer, but on the other hand it gives a huge advantage, because Both variants of structures can coexist quite peacefully. My example will help you when cabinets have one information, and milk has another.
From the point of view of sampling, everything is much simpler on the one hand, and more difficult on the other.
For example, if there is no field, the query simply will not return the data. But you can add a condition under which they will be returned.
The most difficult thing that causes rejection on the part of avid lovers of the relational model is the lack of foreign keys and unions (join). Here it is necessary to radically revise the attitude to data structuring, and many books, articles, etc. have been written about this. You need to be very clear about where and when which model and structure is more beneficial to use.
For example, if you value flexibility and the ability to scale quickly (collect data from thousands of different devices in real time), then MongoDB, but if you need a rigid complex structure, such as CRM with hellish billing and malicious transactions, then you need to choose SQL like PostreSQL or Oracle.
MongoDB is such a good start-up option, in which the database structure has to be changed several times a day, and even under wild load and without downtime. SQL is such a planned enterprise that will send out all notifications for a month, then stop everything, do migrations, then run, test.
Well, if the data is overdone and you decide to do sharding, then real fun will come. MongoDB does this well out of the box, but it has its limitations. In PostgreSQL, this is done through a crutch in the form of an intermediate table, which will have to be protected like the apple of an eye. Again, bulk operations like loading data will unrealistically slow down due to blocking shared resources. MySQL seems to have Galera, but I have never seen it in operation.

Answer 2

Yes, maybe it's time not only for mongo, but also for hbase. Your task, in theory, fits very well on hbase.