Database with extra headers

Question

Does anyone know any database in which, in addition to the traditional architecture (rows and columns), we could create and manipulate FIELDS, which would be available for reading and writing and which could be manipulated via SQL?

Example:

Extra Table Header CLIENTS:

+-----------------+--------+-----------------------------+
|NOME DO CAMPO    | TIPO   | VALOR                       |
+-----------------+--------+-----------------------------+
|VENDAS_01        |DOUBLE  | 0.00                        |
+-----------------+--------+-----------------------------+
|VENDAS_02        |DOUBLE  | 0.00                        |
+-----------------+--------+-----------------------------+
|VENDAS_03        |DOUBLE  | 0.00                        |
+-----------------+--------+-----------------------------+
|TAXA_XPTO        |DOUBLE  | 0.00                        |
+-----------------+--------+-----------------------------+

The table CUSTOMERS:

+-----------------+-----------------+-----------+
|CLIENTE_ID       |NOME             | CEP       |
+-----------------+-----------------+-----------+
|                1|PEDRO SILVA      |03232-000  |
+-----------------+-----------------+-----------+
|                2|PEDRO SOUZA      |03232-001  |
+-----------------+-----------------+-----------+
|                3|PEDRO COSTA      |03232-001  |
+-----------------+-----------------+-----------+

Answer 1

As far as I know no relational database does this "natively". On the other hand the result is possible in any relational database.

I speak in relational because there seems to be a requirement to use SQL that is fundamentally used in relational databases.

Nosql

In fact there are several non-relational databases, in general the so-called Nosql, are made for this. If the feature is important and the data volume is large, it is recommended to opt for a tool like this.

Entity-Attribute-Value

If the use of a relational model is necessary to meet this need, the model can be adopted Entity-Attribute-Value.

The most that a database could do is create some facilities to hide the implementation of this, it would be practically a syntax sugar. I believe that none of them took the trouble to do this not only because it deviates from the standard established in SQL and its use is not universal but also because it is trivial to do this manually.

In fact, your example shows you how to do it. It would have two tables and the queries and data manipulation would happen as shown in the comment, it would only have a slightly different syntax, obvious.

So the database having this done might be a little more productive but not much more. And you’d have a problem, a lot of people don’t quite understand how Dbms work, and they’d probably abuse it thinking it’s done magically and at no cost. It would not be easy for software managers to do anything much better than the programmer/DBA would do manually. Perhaps in some cases the manual can bring better performance.

I have to say that the ideal is to avoid this type of composition, she has performance problems. Of course for certain volumes this is no problem, and if you really need it is a valid tool as long as you know the commitments (where you are getting into).

The way tables are defined works in a certain way as static languages, that is, structures are defined beforehand and the database ensures that it is always used in this way. Using EAV is like using a dynamic language where the data structure is less important and the application should turn around so that everything works right, you should always see if the structure is according to what it expects or should adapt to work with what you found.

When you use this technique you are giving up the scheme that is so important in the relational model. But she’s not considered wrong, she’s actually recommended by sixth form of standardisation. But note that normalization cares about the model and not about performance or other commitments.

Vale a lida in Alternatives to the (anti-)standard Entity-Attribute-Value.

Other solutions

Some databases help this dynamism without using an extra table which can be very advantageous from a performance point of view.

The Sqlite for example allows any column to have any kind of value. It does not mean that this is recommended, but knowing how to use it can be useful. Then you can put these extra columns inside the table itself that they belong to. It doesn’t solve everything but helps. It can also simulate array.

The Postgresql possess arrays that can simulate these extra columns. There are also limitations but it may be sufficient.

In fact any database can do the same using a column varchar to be responsible for storing all these extra columns. You would have to access this data in an appropriate way. Of course, to make it easier, it will be necessary views, triggers, stored procedures and functions. Everything has a price.

In the Wikipedia article of the EAV standard shows that SQL Server 2008 allows columns can be defined without occupying space, what may be useful in some of these scenarios.

The same article shows that there are specific banks in the cloud who can maintain relational and Nosql characteristics.

Depending on what you want, an XML or JSON column, as is common to have on several current systems, may be the solution. It subverts the relational model a little but gives the necessary flexibility. Relational Dbs started putting this kind of column precisely to compete with the Nosql. Some even have other tools that help bring them closer to the unstructured Nosql model. See more on wikipedia article.

You can create a tree structure as I have already I asked here.

But these are just attempts to optimize the EAV model which is probably what will solve what you want.

Completion

Remember that no matter how you use it you will lose or make it difficult to use certain characteristics of a relational DB. This can be critical or derisory.

It may be just an example and I may not have seen the whole but what you showed me seems a case for common columns or for common normalization and not for dynamic columns.

This standard should be used with semi-structured data, that is, it is neither a pure text, nor is it possible to organize the data that will be stored consistently and absolutely standardized. And as demonstrated there are several solutions to this, some helped by DB software.

The solution is there and I think you’ve even thought about it, just not with the ease that you want, maybe because most other people do not want. Surely it’s not because it can’t be done or because no one’s thought of it before.