What is the best way to set a structure for data of different types?

Question

PostgreSQL

So, I have a User model with all the inherent devise's fields.
Each entry must also have a set of some additional fields, the set of which is strictly defined, but at the same time, this set can be changed at the will of the site administrator. Let's say this set is stored in a table named profile_fields.
The most obvious would be to add an additional label like profile_field_values, and everything would be fine, but this label will have a fixed data type for the value column, and I need to make a selection by different types. For example, we have a user vasya who has the following add. fields:

• name: Vasya
  
• date of birth: 7/7/1997
  
• foot size: 43
  

And, say, I need to get a selection of users whose date of birth is more than 1/1/1995 , foot size is less than 45 , name begins with the letter "B" . That is, when sampling, the database needs to know that the date of birth is exactly the date, the size of the foot is a number, and the name is a string. Therefore, the option with one table for key-value pairs for each field is not suitable, since we only have a string value, and each time casting it to the right one, I think, is somehow not very good.
But while I was thinking what can be done here, I remembered such a wonderful thing in Postgresql as json. That is, in fact, we can store a json object with all the values ​​\u200b\u200bthat we need (with certain restrictions, but we can deal with them) in one field and work with it as if we had MongoDB inside. This is by far the most preferred option for me, but there are big questions about the performance and usability of working with such a structure.
The third option I thought was to use a second Mongo database to store user fields, but I don’t like it at all ...
So, the question is: which option do you think is the most suitable and why? Or are all these options for suckers and there is a much more competent solution?

Answer 1

The most suitable option cannot be advised without knowing your data volumes and how you plan to use it.
If the search time is not critical, there are few data types, you need a simple and portable solution, then I would advise option 1 with serialization of various types into a string.
The postgrew json/jsonb option adds flexibility/performance, but in json the simple types are number, string, and boolean. There are no dates or other types. Indexes are supported for jsonb. This decision means a hard link to the PG.
There is another option similar to 1, but with the assignment of columns of different types and storage in each property of its type. Requests will look something like this

SELECT * FROM profile_field_values
WHERE 
  (property_field="дата рождения"
  and value_type="date" and value_date > ?)
and
  (property_field="размер ноги"
  and value_type="integer" and value_integer < ?)

In terms of speed, you will win, but you will get difficulties with the implementation and integration of all this into rails.
There is also a variation on the theme of the N3 solution - an external search service: Sphinx, Solr, etc...

Answer 2

Obviously, the most reasonable solution in this formulation of the question is the json field. IMHO
In the future, if a certain field occurs frequently, it can be added to the User model so that the search for this field is faster.