问题描述

我对这两种结构很困惑.这两个表的优缺点是什么?哪个更好，为什么?

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

推荐答案

反模式?

通常情况下，在数据库设计的上下文中，第二个表是反模式.而且，更重要的是，它有特定的名称:Entity-Attribute-Value (EAV).在某些情况下，使用这种设计是合理的，但这种情况很少见 - 即使在这种情况下也可以避免.

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.

数据完整性支持

尽管如此，这种结构似乎更灵活"；或高级"，这种设计有弱点.

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

• 无法制作强制性属性.您不能强制某些属性，因为属性现在存储为一行 - 并且该属性未设置的唯一标志是表中不存在相应的行.SQL 不允许您在本地构建此类约束 - 因此，您必须在应用程序中进行检查 - 并且，是的，每次都查询您的表
• 混合数据类型.您将无法使用 SQL 标准数据类型.因为您的值列必须是超类型"对于其中的所有存储值.这意味着 - 通常您必须将所有数据存储为原始字符串.然后你会看到处理日期和处理字符串一样痛苦，每次都转换数据类型，检查数据完整性，等等
• 无法强制执行参照完整性.在正常情况下，您可以使用外键来限制您的值，这些值在父表中定义.但不是在这种情况下 - 那是因为参照完整性应用于表中的每一行，但不适用于行值.所以 - 你会失去这个优势 - 它是关系数据库中的一个基本
• 无法设置属性名称.这意味着 - 您不能在 DB 级别正确限制属性名称.例如，您将在第一种情况下将 "customer_name" 写为属性名称 - 另一个开发人员会忘记这一点并使用 "name_of_customer".而且.. 没关系，DB 会通过的，您将花费数小时来调试这个案例.

• 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.

行重建

此外，行重建在普通情况下会很糟糕.例如，如果您有 5 个属性 - 这将是 5 个自表 JOIN-s.对于这种简单的——乍一看——情况来说太糟糕了.所以我什至不想想象你将如何维护 20 个属性.

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.

我的观点是 - 不.在 RDBMS 中，总会有办法避免这种情况.这太糟糕了.如果打算使用 EAV，那么最好的选择可能是非关系数据库.

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.

这篇关于两种表结构的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！