The difference between the two table structures

Question

I am very embarrassed about the two structures. What are the advantages and disadvantages of this table? Which one is better and why?

TABLE1

id, name, age, birthdate, address somedata1 somedata1 somedata1 somedata1 somedata1 somedata2 somedata2 somedata2 somedata2 somedata2 somedata3 somedata3 somedata3 somedata3 somedata3 

TABLE2

 id, col_name, col_value somedata name somedata somedata age somedata somedata birthdate somedata somedata address somedata somedata2 name somedata2 somedata2 age somedata2 somedata2 birthdate somedata2 somedata2 address somedata2 somedata3 name somedata3 somedata3 age somedata3 somedata3 birthdate somedata3 somedata3 address somedata3 

Answer 1

antipattern?

In general, the second anti-pattern table is in the context of database design. And even more, it has a specific name: Entity-Attribute-Value (EAV). There are some cases where the use of this design is justified, but these are rare cases - and even there it can be avoided.

Why eav is bad

Data integrity support

Despite the fact that such a structure seems more "flexible" or "advanced", this design has a weakness.

• Unable to make required attributes . You cannot make a certain attribute mandatory, because the attribute is now stored as a row - and the only sign that this attribute is not set is that the corresponding row is not in the table. SQL will not allow you to build such a restriction initially - thus, you will need to check what is in the application - and, yes, query your table every time
• Mixing data types . You cannot use standard SQL data types. Because the value column must be a "supertype" for all stored values. This means that in general, you will store all data as raw rows. Then you will see how hard it is to work with dates, as with strings, type data types every time, check data integrity, e tc
• Unable to provide referential integrity . In a normal situation, you can use a foreign key to limit your values ​​to those defined in the parent table. But not in this case - because referential integrity applies to every row in the table, but not to row values. So - you will lose this advantage - and this is one of the fundamental with respect to DB
• Unable to set attribute names . This means that you cannot correctly restrict the attribute name at the database level. For example, you write "customer_name" as the attribute name in the first case - and another developer will forget it and use "name_of_customer" . And .. this is normal, DB it will pass, and you will end up with the hours spent debugging this case.

Line reconstruction

Also, line reconstruction will be terrible in the general case. If you have, for example, 5 attributes, it will be 5 self-made JOIN -s. Too bad for such a simple - at first glance - case. Therefore, I do not even want to imagine how you will support 20 attributes.

Could this be justified?

My point is no. There is always a way to avoid this in the DBMS. This is terrible. And if EAV is intended to be used, non-relational databases might be the best choice.

Answer 2

in the second case (table2) it is complicated and takes a lot of time to find data when we make a request for it. this case is used when you do not know about the number of columns or they differ, if you have a fixed column length, then the first case is used (table1), because in this case the data finds a quick way.

Answer 3

The table with the columns id , name , age , birthdate , address is what you use when you know what information to store in the entity before deployment.

A table with columns id , col_name , col_value can be used if you only know after deployment what information to store for the object (for example, if non-technical people should be able to determine the fields that they want to capture). It is less efficient, but allows you to define new fields without changing the database schema.