电话号码属于数据库模型中的什么位置？
给定一个 DVD 租赁商店的模式，客户的电话号码应该属于地址表还是用户表，为什么？一种方法或另一种方法有什么好处吗？
Given a schema for a DVD rental store, should customers' phone numbers belong to the addresses table, or the users table, and why? Are there any benefits associated with one approach or the other?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
为什么您还要拥有一张地址表（除非您想要为某一特定客户提供多个地址）？
您的主要“客户”是客户。您不是将 DVD 租给一个地址，而是租给一个人。当一块土地的占有者带着你珍贵的“自由威利”收藏版三部曲逃跑时，你不能将一块土地告上法庭。
在一个人只住在一个地方的世界中，地址将成为客户表的一部分（在每个客户一台电话的情况下，电话也将是客户表的一部分）。
如果您想要多个地址，那很好，有一个单独的地址表将这些地址与客户联系起来。
但您可能也应该对手机进行类似的设置。在客户表中允许每个客户最多
N个电话号码（具有N列），或者（更好）有一个单独的电话表，允许每个客户包含任意数量的电话号码顾客。像这样的东西：
Why do you even have an addresses table (unless you want more than one address for a given customer)?
You primary "client" is a customer. You don't rent DVDs to an address, you rent them to a person. You can't take a block of land to court when the occupant runs off with your prized "Free Willy" collectors edition trilogy.
In a world where a person only lived at one place, the address would be part of the customer table (and so would the phone in a one-phone-per-customer scenario).
If you want multiple addresses, that's fine, have a separate addresses table tying those addresses back to the customer.
But you should probably also have a similar setup for phones. Either allow up to
Nphone numbers per customer (withNcolumns) in the customers table, or (much better) have a separate phones table allowing any number of phone numbers per customer.Something like:
对此没有一个正确的答案，除了说“这取决于情况”。
这实际上取决于您使用数据库模式建模的内容。电话号码在逻辑上是否属于某个用户，或者可能由多个用户共享的地址？
示例 - 移动电话号码可能与特定人员相关联，因此成为用户表的一部分。固定电话号码可能与特定位置或住所相关，因此是地址的一部分。
There's no one correct answer to this, except to say "it depends".
It really depends on what you're modelling with your database schema. Does a phone number logically belong to a user, or an address that could potentially be shared by multiple users?
Example - a mobile phone number might be tied to a particular person, and so be part of the users table. A land-line number might be tied to a particular location or residence, and so be part of the address.
信息建模的基本案例：
案例A. 每个客户可以有多个电话号码。
在这种情况下，电话号码属于单独的表。
案例A1。客户并不需要有电话号码。即“关系”是 1-1 到 0-n（即假设所有电话号码必须始终“针对”某个客户）。无事可做。
案例A2。事实上，每个客户确实被要求有一个电话号码。您可以将其建模为 1-1 到 1-n 的关系，但是 1-n 部分的“1”在 SQL 系统中很难强制执行（并且在最便宜的 SQL 系统中） ，可能根本不可能）。这并不意味着您不应该按原样正确记录业务规则。
情况 B. 每个客户至多有一个电话号码。
案例B1。每个客户都需要有一个电话号码。这意味着每位客户始终拥有完全相同一个电话号码。电话号码最好放在客户表中。 （请注意，“拥有电话号码”的意思是“拥有相关商店已知的电话号码！）
情况 B2。客户不需要拥有电话号码。在正式关系理论中，它是要求您定义一个单独的表，仅保存已知的电话号码。在 ER 和 UML 等非正式建模技术中，您可以将其建模为“可选属性”，在 SQL 系统中，许多人会为此定义一个可为空的属性。
至于“电话号码“属于”地址”：电话号码和地址之间是否存在与您的业务相关的任何类型的“联系”？我的意思是，假设某个客户有两个地址和两个电话号码。了解其中哪一个很重要吗？两个电话号码属于这两个地址中的哪一个？手机号码“属于”哪个地址？
Basic cases of information modeling :
Case A. Each customer can have more than one phone number.
In this case, phone number belongs in a separate table.
Case A1. It is not the case that a customer is required to have a phone number. i.e. the "relationship" is 1-1 to 0-n (i.e. assuming all phone number must always "be for" some customer). Nothing to do.
Case A2. It is the case that each customer is indeed required to have a phone number. You can model this as a relationship that is 1-1 to 1-n, but the "1" of the 1-n part is very hard to enforce in SQL systems (and in the cheapest of them, probably just impossible). That does not mean that you shouldn't be documenting the business rule properly as it is.
Case B. Each customer has AT MOST one phone number.
Case B1. Each customer is required to have a phone number. This means that each customer always has exactly one phone number. Phone number is best put in the customer table. (Note that "to have a phone number" means "to have a phone number THAT IS KNOWN TO THE STORE in question !)
Case B2. It is not required for a customer to have a phone number. In formal relational theory, it is required that you define a separate table which will hold only the known phone numbers. In informal modeling techniques such as ER and UML, you can model this as an "optional attribute". In SQL systems, many would define a nullable attribute for this.
As for "phone numbers 'belonging' to addresses" : is there any kind of "connection" between phone numbers and addresses that is relevant to your business ? I mean, let's say some customer has two addresses and two phone numbers. Is it important to know which of those two phone numbers belongs to which one of those two addresses ? What address would a cellphone number 'belong to' ?
只是关于您的网站/应用程序的假设，但通常我会说“地址”，因为用户信息往往是您经常提取来运行网站的信息（ID、用户名、访问次数等），而电话号码可能不是？
Just an assumption about your site/app, but usually I'd say "Addresses", because user information tends to be info that you pull out frequently to run the site (ID, username, visits etc) whereas phone number may not be?
你所说的“传统”是什么意思？由于用户可以拥有任意数量的联系电话号码（家庭、工作、个人手机、工作手机、传真等），因此似乎应该有一个单独的电话号码表，其中每一行都包含一个号码和一个值，表明它是什么类型的数字。
What do you mean by "traditional?" Since a user can have an arbitrarily large number of contact phone numbers (home, work, personal mobile, work mobile, fax, etc.), it seems like there should be a separate phone numbers table, each row of which includes a number and a value that says what type of number it is.
众所周知，联系信息很难在关系模式中建模。为了保持理智，我建议您对电话号码做出最少数量的假设。允许一个客户/帐户使用多个电话号码是件好事；除此之外，很难对电话号码应用规则。
有一个众所周知的例外：许多披萨外卖店使用电话号码作为客户的主键。这是可行的，因为一般来说，有一部电话与送披萨的地点相关联。另一方面，许多人不再拥有固定电话，所以也许连这个系统都在崩溃。无论如何，我认为这不适用于 DVD 租赁。
Contact information is notoriously difficult to model in a relational schema. In order to keep your sanity, I would advise that you make a minimum number of assuptions with respect to phone numbers. Allowing multiple phone numbers for one customer/account is good; beyond that it's hard to apply rules to phone numbers.
There is one well known exception: many pizza delivery shops use phone numbers as primary keys for customers. This works because in general there is one phone associated with the place to which one delivers pizza. On the other hand, many people no longer have land lines, so perhaps even that system is breaking down. In any case, I don't think this applies to DVD rental.
多个客户可能拥有相同的电话号码：也许来自同一栋大楼的多个人向您购买商品。
当您更新一位客户的电话号码时会发生什么情况；它是否应该为其他共享该号码的人更新该号码？
More than one customer may have the same phone number: perhaps multiple people from the same building buy from you.
What happens when you update the phone number for one customer; should it update that number for the other people that supposedly share that number?