In relational, it is possible to have EAV (also called a pair of attribute values or name-value pairs). It uses a rather abstract data model. Before describing this, there are a few caveats. it is difficult to request and it does not work well. EAVs are often used in relational audit tables. EAV saves before the image of one column (before changing it). It saves one column after another, which has changed. If the five attributes have changed, five lines will be saved in the EAV. In addition, because of the potentially large natural primary key, EAVs often use mirrored tables instead of EAVs.
A EAV can be created in modeling logical data and in relational. This can be done with three related objects or tables:
- A primary object (such as a client) that is similar to a column family.
- An object of type "type" that describes the attribute and its characteristics, such as Net Worth Amount,
- A value object that assigns an attribute to an instance of the base object and gives it a value.
A basic object is an object that has various characteristics.
A “type” is simply a code table identified by a type code and containing a description and other characteristics of the domain. A domain refers to a data type, length, value and units of measurement, etc. It describes an attribute out of context (i.e., Unassigned). An example is Net Worth Amount, which is the number 8 with 2 decimal places, with the right of alignment, and its description is "a value representing the total financial value of the client, including liquid and illiquid amounts."
A “value” object is an associative object or table identified by a customer identifier and an attribute type code [both foreign keys], which has a value attribute that assigns the type of net value amount to the Client and gives it a value, for example, “$ 2,000,000”. The value column often indicates a common name, such as Field.
However, in SQL, name and value pairs are somewhat difficult to query and, as a rule, work poorly. Let's say that the value object has the field attribute. This is a character string. Normally, SQL uses the column name for the headers of the result set. Say the field has Net Worth. But when it is displayed, the title will be called "Field" and not "No." Field is the actual name of the column. Extended SQL is required to get the desired header. This problem can be solved by denormalizing objects of type and "value" into one. Instead of three tables, you have two - one to many, a base table and a table of values. In fact, this is essentially how Cassandra does it: the columns in the column family are a completely flattened attribute-value pair. Even if you denormalize in relational, the column “field” is still called “field”.
You can also flatten (denormalize) three objects into one table in the relational, but there will be problems with redundancy: client identifier [FK], attribute type code [FK], database attribute, attribute type attributes, field (for values). I do not recommend; I'm just saying.
In the early days of data modeling, data modelers liked to use EAV because they turned out to be an elegant, flexible solution - until the database administrators mastered it. Consequently, EAV is sometimes used for a logical model, but must be completely denormalized in the physical. When he is completely denormalized, he is very similar to Cassandra. I sometimes used EAV both logically and physically, with the above problems. I do not know how to add graphics to these comments or I would include an illustration.