You want to store the user entity with N fields, where N is unknown in advance number 1<=N<=2000. Many fields are repeated (name, email, phone number), but availability is not guaranteed. There is a need at any time to all existing field types in the user.
The upper ceiling plan scale - several million records.
Requires high flexibility of the samples.
The question arose in the select a database:
Relational database, PostgreSQL. Storage options:
1. table user: (id + a JSON field) + a separate table to store the list of fields. To work this, as I understand it, will be very very slow.
2. table user: (id) + table field(primary key, name, value, user_id), foreign keys wrapped on top. Problems: data redundancy -- if a million of users have full name, the table field will be a million fields of view:
[ 999 | name | Ivan | 555 ], ... , [ 9999 | name | Peter | 666 ], ... .
3. table user: (id) + table field (id, name) + field_value table (user_id, field_id, value). Pros: minimal data redundancy, very easy to get a list of all fields. Cons: the number of rows in the table field_value will seek to count(user) * count(field) that with one million and one thousand user field will be equal to billion. It's too much?
Document DB (mongo)
Pros: storage Model fits our data.
Cons: overall, I have been reading a large number of horror stories about Mongu, the ratio is slightly biased; lack of experience; the fear to face the future with too much unrelated data. (I would like to be able to safely link data to other entities (products/tags/etc)
In fact, there is an understanding that a mistake now can be very costly later, so ask for advice. Whether to use one of these or undetected ways with an sql database, or should it proceed to the study of Mongo?
Look closely at the structure of EAV. And the base can be any
Mylene_Bruen91 answered on March 19th 20 at 08:48
Will fit any of the categories Wide-column store. You can try MariaDB ColumnStore.
Natasha19 answered on March 19th 20 at 08:50
If you need one table without dainow several million records, then just store a table in a column oriented DBMS Clickhouse. 2000 column the norm there.
Think about the performance you will have after a billion records