数据库设计：循环引用 [英] Database design: circular references

问题描述

我有三个数据库表：

• 用户


• 电子邮件


• 邀请

电子邮件由user_id字段链接到用户。

邀请也通过user_id字段与用户相关联

电子邮件可以在没有邀请的情况下创建，但每个邀请都必须有电子邮件。

我想链接电子邮件和邀请表，以便可以找到特定邀请的电子邮件。

但是创建一个循环引用，邀请和电子邮件记录保存同一用户的ID。

这是一个糟糕的设计，如果是这样，我该如何改进？ / p>

我的感觉是使用外键和良好的业务逻辑，这是很好的。

 用户
 ----- 
 id 
 
电子邮件
 ------ 
 id 
 users_id 
 
邀请
 ----------- 
 id 
 users_id 
 emails_id 
  

解决方案

这不是一个循环引用。

这将是如果电子邮件与邀请和邀请有一个强大的诚信关系一个独立的强完整性关系回到电子邮件（例如）。

编辑：关于设计

正如亨克·霍特曼指出问题是如果您的设计是正常化到期望的程度。

Assiming 表：主键如

users：id

emails：id， users_id

邀请：id，users_id，emails_id

并假设table_id字段和上的外键没有其他约束放在桌子上（例如只有一部分键是唯一的），那么您已经为每个用户建模了以下内容：

• 可以是几封电子邮件，您不能有电子邮件没有相应的用户记录


• 每个电子邮件可能有几个邀请，您不能有邀请没有相应的电子邮件或用户记录（注意：从上面的定义我们不知道user_id是指电子邮件还是用户中的条目）

现在只有你可以说这些规则是否符合您正在尝试建模的现实情况。

查看数据库设计的一种方法是 - 实际上没有错误的数据库设计，你几乎可以总是找到数据，使某些东西看起来像是错误的。这就是为什么不采用规则（以句子的形式）和表（ER图，表和关系的描述），如果设计中存在问题（尽管可以从个人经验中提出建议）是不可能的。

为了说明 - 上面的注意事项，不清楚哪个表user_id引用可能看起来很容易回答。而且常见的答案是，考虑到你说每个邀请都有一封邮件，那应该是从邮件表中引用user_id。

否则可能会有一个邀请在邀请上记录的user_id和为邮件记录的user_id不同。

通常情况下，这应该会使您的头脑中闪烁着标有标准化数据的红灯。但是，这里常常没有言语的假设是email_id确定user_id，这可能不是真的（！）。

这取决于数据的语义（谓词），例如，如果您正在尝试对可能向一个人发送邀请的情况进行建模，并从另一个人那里接收电子邮件回复（例如通过秘书邀请人并直接回复），则红灯关闭，一切都很好 - 这是真的发生了，这就是你在设计中允许的。

I have three database tables:

• users
• emails
• invitations

Emails are linked to users by a user_id field.

Invitations are also linked to users by a user_id field

Emails can be created without an invitation, but every invitation must have an email.

I would like to link the emails and invitations tables so it is possible to find the email for a particular invitation.

However this creates a circular reference, both an invitation and an email record hold the id for the same user.

Is this bad design and if so, how could I improve it?

My feeling is that with use of foreign keys and good business logic, it is fine.

users
-----
id

emails
------
id
users_id

invitations
-----------
id
users_id
emails_id

解决方案

This is not a circular reference.

It would be if emails would have a strong integrity relationship to invitations and invitations an independent strong integrity relationship back to emails (for example).

EDIT: regarding the design

As Henk Holterman points out the question is if your design is normalized to desired extent.

Assiming tables: primary keys such as

users: id
emails: id, users_id
invitations: id, users_id, emails_id

and assuming foreign keys on table_id fields and that no other constraints are placed on the tables (such for example only a part of a key being unique) then you have modelled the following:

• for each user there can be several e-mails and you can not have emails with no corresponding user record
• for each email there can be several invitations and you can not have invitations with no corresponding e-mail nor user record (note: from the above definition we can not know if the user_id refers to entry in emails or in users)

Now only you can say if those rules correspond to the ones from the real world situation that you are trying to model.

One way to look at the database design is - there is actually no wrong database design, you can almost always find data that would make something that looks like an error justified. That's why without taking both rules (in form of sentences) and the tables (E-R diagram, description of tables and relationships) it is impossible to say if there is a problem in design (though it is possible to give suggestions from personal experience).

To illustrate - the above note that it is not clear which table user_id refers to might seem easy to answer. And the common answer, considering that you said that every invitation has a mail, is that it should refer to user_id from the mail table.

Otherwise there could exist an invitation for which user_id recorded on the invitation and the user_id recorded for a mail are different.

Normally, this should make a red light labelled 'normalize your data' go flashing in your mind. But, often unspoken assumption here is that the email_id determines the user_id, and that might not be true(!).

This depends on the semantics of your data (the predicate of each table) - for example if you are trying to model a situation where it is possible to send invitation to one person, and receive an e-mail reply from another person (for example inviting people through secretary and receiving direct replies), then the red light switches off and all is fine - that is what really happened and that is what you are going to allow for in your design.

这篇关于数据库设计：循环引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！