两个表结构之间的区别 [英] Difference between two table structure

问题描述

我很困惑两个结构。这两个表的优点和缺点是什么？
哪一个更好，为什么？

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 
  
 
 
解决方案
 
 Antipattern？ 
 
 
 
在通常情况下，第二个表在数据库设计的上下文中是反模式。而且，它具有特定的名称：实体 - 属性值（EAV）。在某些情况下，使用此设计是合理的，但这是罕见的情况 - 甚至可以避免。
 
 
 
 
 
 
 
 
为什么EAV不好
 
 
 
 数据完整性支持 
 
 
 
尽管事实上，这种结构似乎更灵活或先进，这种设计有弱点。
 
 
 
 
 
 不可能强制实施。您不能使某些属性是强制性的，因为属性现在存储为一个行 - 并且属性未设置的唯一符号是表中缺少的相应行。 SQL不会允许你在本地构建这样的约束 - 因此，你必须检查应用程序中是否每次都查询你的表 
 
 
 混合数据类型。您将无法使用SQL标准数据类型。因为您的值列必须是其所有存储值的超类型。这意味着 - 您通常将所有数据存储为原始字符串。然后你会看到如何使用字符串来处理日期，每次转换数据类型，检查数据完整性，数据的完整性。 
 
 
 无法强制执行参照完整性。在正常情况下，可以使用外键通过在父表中定义的值来限制值。但不是在这种情况下 - 这是因为参照完整性应用于表中的每一行，但不是行值。所以，你会松开这个优势，并且是关系DB 
 
 
 不可能设置属性名称中的基本之一。这意味着 - 您不能正确地限制属性名称在数据库级别。例如，在第一种情况下，您将写入customer_name作为属性名称 - 另一个开发人员会忘记并使用name_of_customer。 
 
 
 
 
 
 行重建 
 
 
 
此外，行重建在通常情况下会很糟糕。如果你有，例如5个属性 - 将是5自表 JOIN  -s。太糟糕了这么简单 - 乍一看 - 情况。所以我不想甚至想象如何保持20个属性。
 
 
 
 
 
 
 
 
是否有理由？
 
 
 
我的观点是 - 不。在RDBMS中总有一种方法可以避免这种情况。这太糟糕了。如果要使用EAV，则最佳选择可以是非关系数据库。 
 
I am very confuse about the two structure. What are the advantage and disadvantage of this two 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

解决方案
Antipattern?

In common case, second table is anti-pattern in context of database design. And, even more, it has specific name: Entity-Attribute-Value (EAV). There are some cases, when using this design is justified, but that are rare cases - and even there it can be avoided.




Why EAV is bad

Data integrity support

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


Impossible to make mandatory attributes. You can not make some attribute mandatory, since attribute is now stored as a row - and the only sign that attribute is not set - is that the corresponding row absent in table. SQL will not allow you to build such constraint natively - thus, you'll have to check that in application - and, yes, query your table each time
Mixing of data types. You will not be able to use SQL standard data types. Because your value column must be a "super-type" for all stored values in it. That means - you'll have in general to store all data as raw strings. Then you'll see how painful is to work with dates as with strings, casting data types each time, checking data integrity, e t.c. 
Impossible to enforce referential intregrity. In normal situation, you can use foreign key to restrict your values by those, which are defined in parent table. But not in this case - that's because referential integrity is applied to each row in table, but not for row values. So - you'll loose this advantage - and it's one of fundamental in relation DB
Impossible to set attributes names. That means - you can't restrict attribute name on DB level properly. For example, you'll write "customer_name" as attribute name in first case - and another developer will forget that and use "name_of_customer". And.. it's ok, DB will pass that and you'll end with hours spent on debugging this case.


Row reconstruction

In addition, row reconstruction will be awful in common case. If you have, for example, 5 attributes - that will be 5 self-table JOIN-s. Too bad for such simple - at first glance - case. So I don't want even imagine how you'll maintain 20 attributes.




Can it be justified?

My point is - no. In RDBMS there will always be a way to avoid this. It's horrible. And if EAV is intended to be used, then best choice may be non-relational databases. 

                        
这篇关于两个表结构之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！