SQL数据库问题与地址簿表设计 [英] SQL database problems with addressbook table design

问题描述

我正在为我的软件写一个通讯录模块。我有数据库设置到目前为止，它支持非常灵活的地址簿配置。

我可以为我想要的每个类型创建n条目。类型是指这样的数据，如电子邮件，地址，电话等。

我有一个名为contact_profiles的表。

这只有两列：

  id主键
 date_created DATETIME 
  

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

  id PK 
 #profile（contact_profiles.id的外键）
类型VARCHAR描述条目的类型（名称，电子邮件，电话，传真，网站，...）我应该稍后将其更改为SET。 
值文本（包含该属性的值）。 
  

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

目前我必须为每个要检索的值创建一个JOIN。
有没有可能以某种方式创建一个视图，这给我一个结果与类型的列？

所以现在我会得到一些像

  #profile type value 
 1 email [e-mail] 
 1名称Sebastian Hoitz 
 1网站domain.tld 
  

但是得到这样的结果会很好：

  #profile电子邮件名称网站
 1 [e-mail] Sebastian Hoitz domain.tld 
  

最初我不想创建表格布局的原因是，可能总是有添加的东西，我想要能够相同类型的多个属性。

您是否知道是否有可能动态转换？

如果你需要更好的描述，请让我知道。

解决方案

你已经重新发明了一个数据库e设计称为实体属性值。这种设计有很多弱点，包括您发现的弱点：很难以常规格式重现查询结果，每个属性有一列。

以下是您必须做的一个例子：

  SELECT c .id，c.date_created，
 c1.value AS名称，
 c2.value AS电子邮件，
 c3.value AS电话，
 c4.value AS传真，
 c5.value AS网站
 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 join。

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

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

• 存储一个 TEXT BLOB，其中包含所有属性结构使用XML或YAML格式。


• 使用语法数据建模解决方案，如芝麻 ，其中任何实体都可以具有动态属性。


• 放弃数据库并使用平面文件。

EAV和任何这些替代解决方案是很多工作。您应该非常仔细地考虑如果您真正需要在数据模型中具有这种程度的灵活性，因为如果您可以将元数据结构视为相对不变的话，这将非常简单。

I am writing a addressbook module for my software right now. I have the database set up so far that it supports a very flexible address-book configuration.

I can create n-entries for every type I want. Type means here data like 'email', 'address', 'telephone' etc.

I have a table named 'contact_profiles'.

This only has two columns:

id           Primary key
date_created DATETIME

And then there is a table called contact_attributes. This one is a little more complex:

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

I can now link to these profiles, for example from my user's table. But from here I run into problems.

At the moment I would have to create a JOIN for each value that I want to retrieve. Is there a possibility to somehow create a View, that gives me a result with the type's as columns?

So right now I would get something like

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

But it would be nice to get a result like this:

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

The reason I do not want to create the table layout like this initially is, that there might always be things to add and I want to be able to have multiple attributes of the same type.

So do you know if there is any possibility to convert this dynamically?

If you need a better description please let me know.

解决方案

You have reinvented a database design called Entity-Attribute-Value. This design has a lot of weaknesses, including the weakness you've discovered: it's very hard to reproduce a query result in a conventional format, with one column per attribute.

Here's an example of what you must do:

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');

You must add another LEFT OUTER JOIN for every attribute. You must know the attributes at the time you write the query. You must use LEFT OUTER JOIN and not INNER JOIN because there's no way to make an attribute mandatory (the equivalent of simply declaring a column NOT NULL).

It's far more efficient to retrieve the attributes as they are stored, and then write application code to loop through the result set, building an object or associative array with an entry for each attribute. You don't need to know all the attributes this way, and you don't have to execute an n-way join.

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

You asked in a comment what to do if you need this level of flexibility, if not use the EAV design? SQL is not the correct solution if you truly need unlimited metadata flexibility. Here are some alternatives:

• Store a TEXT BLOB, containing all the attributes structured in XML or YAML format.
• Use a semantic data modeling solution like Sesame, in which any entity can have dynamic attributes.
• Abandon databases and use flat files.

EAV and any of these alternative solutions is a lot of work. You should consider very carefully if you truly need this degree of flexibility in your data model, because it's hugely more simple if you can treat the metadata structure as relatively unchanging.

这篇关于SQL数据库问题与地址簿表设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！