A
A
Anton178922017-07-25 16:41:11
MySQL
Anton17892, 2017-07-25 16:41:11

DB design. One to many MYSQL or Postgresql JSONB?

Hello.
The task is to create the most optimal database structure for the project. The bottom line is that there are different types of records with an almost similar structure (70%), sometimes having parental relationships with each other (so far max. 3 levels of nesting), as well as relationships with other tables. Spread across different tables is not an option, the basis is a selection of all records with the ability to filter by type. I would not want to put everything in one table, because empty columns will remain (there are cases of inheritance when the necessary information is taken from the parent, and all fields remain empty except for those needed by the child).
Based on all this, there are 2 options so far:
Option 1
1) Make a table with records:
- ID
- User ID
2) Make a table with varchar record fields
-
ID -Key -Value
-Record
ID
3) Make a table with text type fields (for text, Json)
-
ID -Key -Value
-Record
ID
4) Make a table with record dates (or store dates in Unix in the varchar table (pt. 2), in order to avoid an extra query and this table - advice is needed here)
-ID
-Key
-Value -Record
ID
Option 2
Go to postgresql and use jsonb in an example format:
-ID
-Data ( json)
-User ID

Flexibility in requests is important, because there are a lot of filtering criteria, relationships with other tables, and a search is also needed. And also important is the speed and consumption of resources, and also the prospect of optimal expansion.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
akzhan, 2017-07-25
@akzhan

Still, find out what is more important to you, performance, deduplication, or convenience of extension.
The JSONB option is handy, but it doesn't document the structure, which is very important.
Look towards table inheritance , for example.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question