How to store such data?

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?
March 19th 20 at 08:42
4 answers
March 19th 20 at 08:44
Solution
ID (UNSIGNED INTEGER) AUTOINCREMENT, PRIMARY KEY
USER_ID (UNSIGNED INTEGER)
RECORD_ID (UNSIGNED INTEGER)
FIELD_ID (UNSIGNED INTEGER)
FIELD_VALUE (VARCHAR)
March 19th 20 at 08:46
Solution
Look closely at the structure of EAV. And the base can be any
March 19th 20 at 08:48
Solution
Will fit any of the categories Wide-column store. You can try MariaDB ColumnStore.
Or Clickhouse, too, from the Wide-column store, as advised @Natasha19 . - Mylene_Bruen91 commented on March 19th 20 at 08:51
March 19th 20 at 08:50
Solution
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
Well, in fairness, I have noticed that the Join in ClickHouse is still there. Also part of Join queries can be transformed into a query with a subquery. - berniece commented on March 19th 20 at 08:53

Find more questions by tags Data storageMongoDBNoSQLPostgreSQL