多对多关系
我有两张表，客户和银行帐户，具有多对多关系（每个用户可以有多个帐户，一个帐户可以属于多个用户）。但是，该表的某些属性（例如客户表中的电子邮件）为空（大多数客户没有电子邮件）。我该如何解决这个问题？我要创建一个新表吗？
I have two tables, customers and bank accounts, with a many-to-many relationship (each user can have more than one account and an account can belong to multiple users). However, some of the attributes of the table, such as email in the customers table, are empty (most customers don't have an email). How can I fix this? Do I create a new table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
虽然创建稀疏列不会损坏您的数据库，但我建议不要这样做。首先，允许空值违反了高范式，出于数据完整性的原因应该避免。
其次，一个客户可能有多个电子邮件地址，因此电子邮件地址不是关系代数中客户的“属性”，不能由单个客户列来处理。
我建议创建一个单独的 Email 表，使用唯一的主键和 customerID 作为非唯一的外键。添加与客户拥有的电子邮件数量相同的电子邮件记录。
While creating sparse columns will not corrupt your database, I would advise against it. First, allowing nulls violates the higher-normal forms and should be avoided for data-integrity reasons.
Second, a customer may have multiple email addresses, so email address are not "attributes" of a customer in relational algebra and cannot be handled by a single Customer column.
I suggest creating a separate Email table, with a unique primary key and customerID as non-unique foreign key. Add as many Email records as a customer has emails.
否。在“客户”表中，将该字段设置为空或 0。如果客户有多个电子邮件，请创建一个新表“电子邮件”并将其以一对多关系链接到“客户”表。
No. In the table customers let the field null or 0. If a customer would has more than one e-mail, create a new table "emails" and link it to "customers" table in a one-to-many relationship.
数据库列主要由 NULL 值组成并没有什么问题。它被称为稀疏列。
您可以将可选列放在单独的数据库表中。如果附加键和主索引的开销小于所有 NULL 值“浪费”的存储量，您甚至可以使用更少的存储空间。这不太可能，因为现代数据库优化稀疏列以使用最小的存储空间。
您必须为客户表和/或银行帐户表中的每个稀疏列定义一个单独的数据库表。
There's nothing wrong with having a database column consisting of mostly NULL values. It's called a sparse column.
You could put the optional column in a separate database table. If the overhead of additional keys and a primary index is less than the amount of storage "wasted" by all of the NULL values, you could even use less storage space. It's not likely, since modern databases optimize sparse columns to use minimal storage space.
You would have to define a separate database table for each sparse column in your customer table and / or bank account table.