How to store attribute values ​​of various types as EAV?

Question

Could you tell us how to store attribute values ​​of another type as EAV?

Now I see 3 options.

• To store in the same table in different fields something like: entity_id attribute_id string_value numberic_value date_value.
• For storing attribute values ​​of different types in different tables, e.g. string_attribute_values, numeric_attribute_values, date_attribute_values.
• To store values ​​of all types as VARCHAR, but this option seems unacceptable, because it is difficult to filter, for example, by a numerical value (they will be compared as strings), and this allows, for example, to enter a letter in the fields where there should be only numbers.

I suppose I need now: string, numberic (integer) and date type, but maybe other types will come later.

Thank.

Answer 1

In the RDBMS community, EAV is antipattern. For the “flexible flexibility” obtained using EAV, SQL is required for data recovery, since records / objects are much more complex. You also benefit from the fact that you cannot use any available data integrity tools (foreign keys, control restrictions, etc.). Over time, this project collapses under its own weight.

If you have a use case when data elements are part of a supertype / subtype relationship, but they do not change much, then model the tables according to your needs:

1.) Table = . , .

2.) = . , , .

3.) = 1 . , , NULL. , , /.

3, .

EAV, XML RDBMS, EAV ( ). EAV, RDBMS - , . Document/NoSQL/Key-Value, MongoDB, Cassandra .. RDBMS, EAV, , .

, , , , / ..