弱实体的数据库建模 [英] Database modeling for a weak entity

问题描述

我在我的数据库中有两个表订单和 orderHistory 。

I have 2 tables in my database orders and orderHistory.

 -----------------                    -----------------------
 |  orders       |                    |  orderHistory       |
 -----------------                    -----------------------
 | orderID  (PK) |                    | historyLineID  (PK) |
 | orderDate     |                    | status              |
 | price         |                    | quantity            |
 -----------------                    -----------------------

现在订单可以有多个历史记录。但是，历史行不能单独存在。我听说这被称为弱实体，因此来自订单的 PK 必须是表的 PK code> orderHistory 。

Now an order can have multiple history lines. However, a history line can't exist on its own. I heard this is called a weak entity and therefore the PK from orders must be part of the PK of table orderHistory.

问题

1. 这真的是一个正确的弱实体关系吗？是否有其他方法来识别它们？


2. 我应该将表 order 的 PK code> orderHistory 并将其设为复合主键？


3. 如果我决定向 orderHistory添加新记录，我将如何添加一个新的复合键？ （ orderID 可从表订单获取，但 historyLineID


4. 如果我决定将此建模为正常的一对多关系，其中 orderID 作为外键仅添加？


5. 如果所有表格都处于第3个正常形式，那么在设计后期会忽略Weak实体的任何问题？

1. Is this really a correct weak entity relationship? Is there other ways to identify them?
2. Should I add the PK of table order to table orderHistory and make it a composite primary key?
3. In case I decide to add a new record to orderHistory, how will I add a new composite key? (orderID is available from table orders, but historyLineID should be auto incremented.)
4. What if I decide to model this as a normal One-To-Many relationship where orderID is added as a foreign key only instead? what are the cons of doing so?
5. Will ignoring Weak entities at all cause any problems later in a design provided all tables are in 3rd normal form?

请注意

两者 orderID & historyLineID 是代理键。
提前感谢。

Both orderID & historyLineID are surrogate keys. Thanks in advance.

推荐答案

实体不是弱的，因为它不能独立存在，不能独立确定。因此，领导到弱实体的关系被称为识别关系。实际上，这意味着父级的主键会迁移到（通常是适当的）子集孩子的PK（术语弱实体通常是相对于主键定义的，尽管它在理论上可以应用于任何键）。

An entity is not weak because it can't exist independently, but because it can't be identified independently. Therefore, a relationship that "leads" to a weak entity is called "identifying" relationship. In practice, this means that the parent's primary key is migrated into (usually proper) subset of child's PK (the term "weak entity" is usually defined in relation to primary keys, though it could in theory apply to any key).

一个不能独立存在，但可以独立识别的实体 - 换句话说，它与一个非空的非识别关系。

It is perfectly legit to have an entity that can't exist independently, but can be identified independently - in other words, that is in a non-identifying relationship to a non-NULL.

可以单独或与 orderID 组合使用 historyLineID

这真的是一个正确的弱实体关系吗？

Is this really a correct weak entity relationship?

你向我们展示的不是一个弱实体 - 父母的PK不会迁移到孩子的PK。

What you have shown us isn't a weak entity - parent's PK is not migrated into the child's PK.

有其他方法来识别它们吗？

Is there other ways to identify them?

：

• orderHistory 有一个复合PK： {orderID，historyLineID} ，其中 orderID 是FK。 BTW，这个PK可以被认为是自然的：

• orderHistory has a composite PK: {orderID, historyLineID}, where orderID is FK. BTW, this PK could be considered "natural":

orderHistory 有一个替代PK： { orderHistoryID} ，而 orderID 在PK之外。您仍需要使用备用金钥 {orderID，historyLineID} ：

orderHistory has a surrogate PK: {orderHistoryID}, while orderID is outside of the PK. You'd still need to have an alternate key {orderID, historyLineID} though:

我应该将表顺序的PK添加到表orderHistory，并将其作为复合主键？

Should I add the PK of table order to table orderHistory and make it a composite primary key?

是的，这是上述第一个选项。除非你在 orderHistory 本身有子关系，这也是最好的解决方案。如果 orderHistory 确实有孩子，根据几个因素，这可能是也可能不是最好的解决方案。

Yes, this is the first option described above. Unless you have child relationships on orderHistory itself, this is also the best solution. If orderHistory does have children, than this may or may not be the best solution, depending on several factors.

如果我决定将此模型建模为正常的一对多关系，如果将orderID添加为外键，该怎么办？这样做的缺点是什么？

What if I decide to model this as a normal One-To-Many relationship where orderID is added as a foreign key instead? what are the cons of doing so?

这不是 - 或。一个字段可以是FK和（主键或备用）键的一部分，如上所示。

This is not either-or. A field can be both FK and a part of a (primary or alternate) key, as shown above.

将忽略Weak实体如果所有表格都是第三个正常形式，会在后面的设计中产生任何问题？

Will ignoring Weak entities at all cause any problems later in a design provided all tables are in 3rd normal form?

您将无法达到3NF，除非正确指定您的密钥，如果不考虑哪些实体可以单独标识，哪些不能标识，您将无法执行此操作。

You won't be able to reach 3NF unless you specify your keys correctly, and you won't be able to do that without considering which entity can be identified independently and which can't.

这篇关于弱实体的数据库建模的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！