具有“主”属性的子集合的最佳架构设计是什么？实体
场景如下：您有一个 Persons 表，它与 Addresses 表具有一对多关系，其中 Address 行之一是“主”Address。
在规范化模式中，
- 使用 Persons.PrimaryAddressID 访问人员的“主要”地址
或
- 使用 Addresses.IsPrimary 位列通过 Addresses.PersonID
或
- 其他
引用人员的“主要”地址是否更好？为什么？
Here is the scenario: You have a Persons table with a one-to-many relationship with an Addresses table, where one of the Address rows is the "primary" Address.
Is it better in a normalized schema to
- Use a Persons.PrimaryAddressID to access the "primary" Address for a Person
or
- Use an Addresses.IsPrimary bit column to reference the "primary" Address for a Person via Addresses.PersonID
or
- Other
and why?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这取决于人与地址的关系是一对零加还是一对一加。
如果一个人需要有一个主要地址，我会将其放在
Persons表本身中（因为它是必需的属性）。另一方面，如果一个人可以在没有地址的情况下存在于您的架构中，我会将
Addresses表中的所有地址保留为相等，并使用Persons表的属性选择主地址（NULL 或指向相关Addresses行的指针）。如果您将地址的素数存储在
Addresses表中，那么当 Bob Smith 的两个地址都声称是主地址时，您会怎么做？您可以使用触发器来阻止这种情况，但正确设计模式会更有效。而且，如果两个室友共用同一个地址，但其中一个一直住在那里，而另一个大部分时间都与女友同居，那么会发生什么呢？如果素数位于“地址”表中，您将无法在人员之间共享地址行。
我想要表达的是，您需要将属性分配给正确的对象。一个人的主要地址属于一个人，而不是一个地址。
为了获得最大的效率和灵活性，我将采用以下模式：
您遇到了一个轻微的数据完整性问题，即
Persons.PrimaryAddressId可能是一个悬挂指针。您不能将其设为主键之一的外键，因为您希望它允许NULL。这意味着您必须考虑到它可能指向不存在的Addresses.Id的可能性。我只需将其修复为
Addresses上的删除前触发器，以便更新相关的Persons行（将PrimaryAddressid设置为 NULL）。或者，您可能会比较狡猾，在
Addresses表中设置一个“未知”地址，以便Persons中的每一行至少有一个地址（主要地址自动未知的地址）将它们的PrimaryAddressid设置为“未知”地址行，然后您可以使其成为适当的约束关系并在一定程度上简化您的 SQL 在现实世界中，实用主义通常胜过教条主义:-)
It depends on whether the person-to-address relationship is one-to-zero-plus or one-to-one-plus.
If a person is required to have a primary address, I would put it in the
Personstable itself (since it's a required attribute).In the other hand, if a person can exist in your schema without an address, I would leave all addresses in the
Addressestable as equal and use an attribute of thePersonstable to select the primary (either NULL or a pointer to the relevantAddressesrow).If you store the primality of an address in the
Addressestable, what do you do when two addresses for Bob Smith both claim to be the primary? You could stop that with triggers but it's far more efficient to design your schema properly.And, if two room-mates share the same address, but one lives there all the time and the other spends most of his time shacked up with his girlfriend, what happens then? If the primality is in the Addresses table, you won't be able to share address rows between persons.
What I'm trying to get across is that you need to allocate your attributes to the right objects. A person's primary address belongs to a person, not an address.
For maximum efficiency and flexibility, I would have the following schema:
You have the minor data integrity problem that
Persons.PrimaryAddressIdmay be a hanging pointer. You can't make it a foreign key to one of the primary keys since you want it to allowNULL. That means you'll have to cater for the possibility that it might point to a non-existentAddresses.Id.I would simply fix that as a before-delete trigger on
Addressesso that the relevantPersonsrows are updated (settingPrimaryAddressidto NULL).Or you could be tricky and have one address of "Unknown" in the
Addressestable so that every row inPersonshas at least one address (those whose primary address is unknown automatically get theirPrimaryAddressidset to the "Unknown" address row.Then you could make it a proper constrained relationship and simplify your SQL somewhat. Pragmatism often beats dogmatism in the real world :-)
我会选择“使用 Persons.PrimaryAddressID 访问人员的“主要”地址”。
仅当人员链接到地址时，主要地址才有意义。所以它应该属于Person。考虑以下第二种方法失败的场景。
a) 地址与另一个实体一起使用，而不引用个人，其中 Addresses.IsPrimary 毫无意义。
b) 同一地址由两个人使用，其中第一个用作主要地址，而第二个则不是。
I would go for "Use a Persons.PrimaryAddressID to access the "primary" Address for a Person".
Primary Address has a meaning only when Person is linked to Addresses. So it should belong to Person. Think about following scenarios where 2nd approach fails.
a) Address is used with another entity without referenced to person where Addresses.IsPrimary is meaningless.
b) Same address is used by two persons where 1st uses as primary while 2nd not.
如果您希望约束是一个人最多有一个主要地址，则
Persons.PrimaryAddressID显然更简单 - 事实上，它是由架构强制执行的。确保每个人只有一个主要地址也很容易（只需使该列不为空），如果需要，甚至可以说没有两个人可以共享一个主要地址（只需使该列是独一无二的）。当然，正是因为这种方法擅长强制执行此类简单的约束，所以当您不想要这些约束时，情况就很糟糕 - 例如，如果您想让一个人能够拥有超过一个“主要”地址，所讨论的方法就行不通。
顺便说一句，我不认为一个人/多个地址的关系特别好除非你想强制执行这样一个事实：没有两个人可以共享相同的地址：一般来说，在正常化的情绪中，我宁愿有一张人员表，一个地址，一个关系表（在大多数情况下自然是多对多，因为许多人可以并且在现实生活中确实共享相同的地址） 。
如果您选择走这条路线，那么，特别是如果您需要高度灵活性（多个主地址和c），让关系表携带“素数”布尔值将是一个有吸引力的选择（它仍然使得执行起来不太困难）上述一些约束，尽管其他约束，例如“一个地址属于至少一个人”或反之亦然，可能很难简单地表达）。
要记住的教训：准确地表达您的模式需要简单地表达哪些约束，并且用于该目的的正确模式通常会非常清楚地出现。如果感兴趣的约束是一个谜，那么“什么是正确的模式”问题的答案也将是一个谜；-)。
If you want the constraint to be that one person has at most one primary address, a
Persons.PrimaryAddressIDis clearly simpler -- indeed, it's enforced by the very schema. It's also easy to ensure exactly one primary address per person (just make that column be not null), and if you need to, even say that no two people can share a primary address (just make that column be unique).Of course, exactly because this approach excels at enforcing such simple constraints, it's bad when you don't want these constraints -- for example, if you want to make it possible for a person to have more than one "primary" address, the approach in question would not work.
Incidentally, I would not consider a one-Person/many-addresses relationship to be particularly good unless you want to enforce the fact that no two people can share the same address: in general, in a normalizing mood, I'd rather have a table of people, one of addresses, and one for the relationship (which in most contexts would naturally be many-to-many, since many people can, and in real life do, share the same address).
If you choose to go this route, then, especially if you need high flexibility (multiple primary addresses &c), having the relationship table carry the "primality" boolean would be an attractive choice (it still makes it not too hard to enforce some of the above-mentioned constraints, though other constraints, such as "an address belongs to at least one person" or vice versa, can be tricky to express simply).
Lesson to retain: express exactly what constraints your schema needs to express simply, and the right schema for that purpose often emerges quite clearly. If the constraints of interest are a mystery, so will be the answer to the question "what's the right schema";-).