数据库设计：成员表分开或全部在一个表中？ [英] DB design : members table separate or all in one table?

问题描述

我想用个人信息创建一个朋友表，并登录详细信息。

I want to create a table of friends with personal information and log on details.

最好将会员表分为两个表，
一个包含最少的细节，
秒与其他细节。

What better to separate the members table to 2 tables , one contain minimal details , second with Other details.

或保留在一个表中？

我有很多表包含会员的外键。

i have a lot of tables that contain the foreign key of the member.

推荐答案

这取决于这些其他的细节。这是一个常见有趣的问题，乍一看没有硬而快的答案。但是，如果我们更抽象地考虑这个问题，关于你想表示的任何特定事物的属性（细节）之间的实际关系，我们可能会发现一些清晰度。

It depends a lot on what those "other" details are. This is a common and interesting question, and there is no "hard and fast" answer at first glance. But if we think of the issue more abstractly, about the actual relationship among the attributes ("details") of any particular thing you want to represent, we may find some clarity.

在你的问题中你说，朋友有最小和其他的细节。将这些细节分类为最小或其他，而不是通过任何使得朋友独特的任何个体（原子）细节完全确定来分类它们。

In your question you state that friends have "minimal" and "other" details. Rather than classifying these details as "minimal" or "other", let's classify them by whether or not any individual ("atomic") detail can be fully determined by whatever makes a friend unique.

我认为有一些主键（PK），如FriendID或电子邮件地址等等。考虑到这个唯一的标识符，问问自己：如果我被给予一个FriendID（或电子邮件或任何您正在使用的PK），我绝对确定的那个朋友的细节？例如，给予FriendID = 2112，我绝对知道朋友的名字，姓氏和出生日期，但我不要绝对知道朋友的电话号码，因为他们有不止一个。

I presume there is some primary key (PK), like FriendID or e-mail address or something. Considering this unique identifier, ask yourself: "If I'm given exactly one FriendID (or e-mail or whatever you are using as PK) what details of that friend am I absolutely sure of? E.g., given FriendID=2112, I absolutely know that friend's first name, last name, and date of birth, but I do not absolutely know that friend's phone number because there is more than one of them.

在一个表中组合所有您明确知道的所有细节给予PK。在小孩表中填写您需要更多数据（如电话号码的家庭或工作）的详细信息，外键返回到PK上的父表（注意：子表的PK很可能是复合的;也就是说，由父表的PK和差异因素（如home或在这个例子中的工作）。1-M关系的许多方面的复合键是非常好的。）

Group together in one table all the details you unambiguously know given the PK. Put the details for which you need more data (like "home" or "work" in the case of phone numbers) in "child" tables, foreign-keyed back to "parent" table on the PK. (Note: It's extremely likely that the PK of the child table will be composite; that is, composed of the parent table's PK and the differentiating factor (like "home" or "work" in this example). Composite keys for the many side of 1-M relations are very good.)

数据库极客将这种分解称为基于功能依赖关系。

Database geeks call this decomposition based on functional dependencies.

这篇关于数据库设计：成员表分开或全部在一个表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！