Operation

This pattern goes against what the relational model establishes. In fact it is very simple.

Instead of having the tables and columns, as we know well in the relational model, there are some tables with basically two columns, a key pair and value.

In the key there is an identification of what column of data you are using there and a unique identifier of what the line would be (something like id that we usually use in tables). Eventually it is possible for this key to be separated into two columns.

The value is equal to what you would have in a column itself. But as in most databases the column type has static type, you have to opt for some convention to write all data. It can be a character type with no border size.

Obviously you need a data dictionary indicating the columns, the actual data types so that you can convert when you need the data typed "right" and provide other relevant information. This dictionary can be in the database or just in the application.

There are extreme cases that there is only one table and the key also has additional information identifying which data table that data refers to.

Think of something like this:

CREATE TABLE TABELA (
    TABELA INT NOT NULL,
    COLUNA INT NOT NULL,
    LINHA INT NOT NULL,
    VALOR VARCHAR,
    PRIMARY KEY (TABELA, COLUNA, LINHA));

Everything is recorded there. In some cases the table and column identifiers are text with the name, which can be even worse by generating slower access and taking up more space.

If it had tables like this:

CREATE TABLE PESSOA (
    ID INT NOT NULL PRIMARY KEY,
    NOME VARCHAR(60),
    NASCTO DATE,
    SALARIO MONEY);

CREATE TABLE DIVIDA (
    ID INT NOT NULL PRIMARY KEY,
    CLIENTE INT,
    VENCTO DATE,
    VALOR MONEY);

Transposing to EAV would be recorded like this:

INSERT INTO TABELA (TABELA, COLUNA, LINHA, VALOR) VALUES
                   (1, 1, 1, "JOÃO"), //nome da pessoa linha 1
                   (1, 2, 1, "15/07/1980"), //nascto
                   (1, 3, 1, "2000,00"), //salario
                   (1, 1, 2, "JOSÉ"), //linha 2 da pessoa
                   (1, 2, 2, "28/10/1986"),
                   (1, 3, 2, "1500,00"),
                   (2, 1, 1, "1"), //tabela divida, relaciona pessoa 1
                   (2, 2, 1, "20/08/2016"), //vecto
                   (2, 3, 1, "100,00"); //valor

Imagine the difficulty of relating to this data. Imagine how optimizations are more complicated. Imagine how much confusion will occur for all data being written to text using a technique called stringly typed .

To use right you almost have to reproduce "in the hand" what the database already does for you.

When to use

Of course this has its usefulness there. Nothing is so bad that there is no use case. You need to know how to use it the right way when it solves a problem that can not be solved better. The problem occurs when the person adopts this because he does not know what he is doing.

Whenever you need flexibility in the template schema, it is appropriate. This way it overcomes the rigidity of the relational model. This example is not even suitable since it simulates the relational model, but I did it to better understand how it works from the relational.

It makes more sense when the end user can determine the columns that need to be used. Whether by choice between various options, or by free creation. If it is by choice between options will compensate more if there really are several options and almost always will be used. In free-to-do, there's no other way.

Some consider NoSQL as an alternative to EAV ( operation ). Actually NoSQL is the ready-to-use EAV (at least in some NoSQL modalities). The term NoSQL is a little misused, in fact what they want to say is NoRelational. One of the great advantages of NoSQL is that it has no schema.

In fact if it is to use so, there is a great chance that a database called NoSQL will be a better solution. But you only need this. In a relational database (SQL or not) it is easy to opt for parties to be like that, not parties. I do not know if you can have the same flexibility in most banks called NoSQL.

Some relational databases allow optimizations for "open schema" scenarios. One example is SQL Server, I do not know if Oracle has something like this. The simplest banks have not.

Articles

Wikipedia article .

Example of full article use .

One more article .

A model comparison .

Answer in OS showing some difficulties .

The name by itself is already very explanatory. This pattern serves to bring a certain dynamism in the structure of the data that will be saved in the database.

As you may well know, a table is usually created to represent a given information, where each column in this table represents an attribute of the data saved. Take a table with Pessoas as an example.

Pessoas
Id      Nome      Idade      Sexo      Twitter      Facebook
---------------------------------------------------------------
1       Mario     37         M         @mario.2     fb.com/ma
2       José      21         N         @jose.3      fb.com/ze

The EAV standard gives you the possibility to "disengage" this information so that Pessoa attributes are no longer represented by columns and are represented by lines in another table. The relationship of an entity to attributes and values is then made through relationships between tables.

Note that using this approach attributes can be created "at runtime", after all it is not necessary to change the structure of your table to add new attributes. And also, I think it's important to note that data integrity gets a little compromised, after all, in most cases the column that saves the value of the attributes will be text type.

See an example:

Pessoas           Atributos          Valores
Id      Nome      Id      Descr      IdPessoa    IdAtributo      Valor
--------------   ----------------   --------------------------------------
1       Ari       1       Idade      1           1               37         
2       José      2       Sexo       1           2               M
                  3       Twitter    1           3               @mario.2
                  4       Facebook   1           4               fb.com/ma
                                     2           1               21
                                     2           2               M
                                     2           3               @jose.3
                                     2           4               fb.com/ze

See also this question:

• Alternatives to the (anti) standard Entity-Attribute-Value