地址簿表设计中的SQL数据库问题

Question

我现在正在为我的软件编写地址簿模块。到目前为止，我已经设置了数据库，以使其支持非常灵活的地址簿配置。

我可以为每种类型创建n个条目。类型是指“电子邮件”，“地址”，“电话”等数据。

我有一个名为“ contact_profiles”的表。

它只有两列：

id           Primary key
date_created DATETIME

然后有一个名为contact_attributes的表。这有点复杂：

id       PK
#profile (Foreign key to contact_profiles.id)
type     VARCHAR describing the type of the entry (name, email, phone, fax, website, ...) I should probably change this to a SET later.
value    Text (containing the value for the attribute).

我现在可以链接到这些配置文件，例如从用户表中。但是从这里我遇到了问题。

目前，我必须为要检索的每个值创建一个JOIN。是否有可能以某种方式创建一个View，使我得到类型为列的结果？

所以现在我会得到类似

#profile type    value
1        email   [e-mail]
1        name    Sebastian Hoitz
1        website domain.tld

但是得到这样的结果将是一件好事：

#profile email           name            website
1        [e-mail] Sebastian Hoitz domain.tld

我最初不想像这样创建表布局的原因是，可能总是要添加一些东西，并且我希望能够具有相同类型的多个属性。

那么，您知道是否有可能进行动态转换？

如果您需要更好的描述，请告诉我。

Answer 1

您已经重新发明了一个名为Entity-Attribute-Value的数据库设计。这种设计有很多缺点，包括您已经发现的缺点：很难以常规格式来复制查询结果，每个属性只有一列。

这是您必须执行的操作的一个示例：

SELECT c.id, c.date_created,
 c1.value AS name,
 c2.value AS email,
 c3.value AS phone,
 c4.value AS fax,
 c5.value AS website
FROM contact_profiles c
 LEFT OUTER JOIN contact_attributes c1
  ON (c.id = c1.profile AND c1.type = 'name')
 LEFT OUTER JOIN contact_attributes c1
  ON (c.id = c1.profile AND c1.type = 'email')
 LEFT OUTER JOIN contact_attributes c1
  ON (c.id = c1.profile AND c1.type = 'phone')
 LEFT OUTER JOIN contact_attributes c1
  ON (c.id = c1.profile AND c1.type = 'fax')
 LEFT OUTER JOIN contact_attributes c1
  ON (c.id = c1.profile AND c1.type = 'website');

您必须LEFT OUTER JOIN为每个属性添加另一个。您在编写查询时必须知道属性。您必须使用LEFT OUTER JOIN而不是，INNER JOIN因为没有办法使属性成为强制性的（等同于简单地声明一列NOT NULL）。

检索存储的属性，然后编写应用程序代码以遍历结果集，为每个属性创建一个对象或关联数组的效率要高得多。您无需以这种方式知道所有属性，也不必执行n-way连接。

SELECT * FROM contact_profiles c
  LEFT OUTER JOIN contact_attributes ca ON (c.id = ca.profile);

您在评论中问，如果不使用EAV设计，如果需要这种灵活性，该怎么办？如果您确实需要无限的元数据灵活性，则SQL不是正确的解决方案。以下是一些替代方案：

• 存储一个TEXTBLOB，其中包含以XML或YAML格式构造的所有属性。
• 使用Sesame之类的语义数据建模解决方案，其中任何实体都可以具有动态属性。
• 放弃数据库并使用平面文件。

EAV和任何其他替代解决方案都需要大量工作。如果您确实需要数据模型中的这种灵活性，则应该仔细考虑，因为如果您可以将元数据结构视为相对不变的话，这要简单得多。