可为空的外键不好的做法? [英] Nullable Foreign Key bad practice?

问题描述

假设您有一个表 Orders，其中有一个指向 Customer Id 的外键.现在，假设您想添加一个没有客户 ID 的订单(这是否可能是另一个问题)，您必须将外键设为 NULL...订单和客户?尽管关系是 1 到 n，但链接表会将其设为 n 到 n.另一方面，使用链接表，我不再有那些 NULL...

Let's say you have a table Orders with a foreign key to a Customer Id. Now, suppose you want to add an Order without a Customer Id, (whether that should be possible is another question) you would have to make the foreign key NULL... Is that bad practice or would you rather work with a link table between Orders and Customers? Although the relationship is 1 to n, a link table would make it n to n. On the other hand, with a link table, I don't have those NULLS anymore...

数据库中实际上不会有很多 NULL，因为外键为 NULL 的记录只是暂时的，直到添加订单的客户.

There won't actually be a lot of NULL's in the database, because a record with a foreign key to NULL is just temporarily until a customer for the order is added.

(就我而言，它不是订单和客户).

(In my case it isn't an Order and a Customer).

要链接到的未分配客户怎么样?

What about a unassigned Customer to link to?

推荐答案

拥有链接表可能是更好的选择.至少它不违反规范化BCNF(Boyce-Codd 范式).不过我更喜欢务实.如果这些空值很少并且它们只是临时的，我认为您应该跳过链接表，因为它只会增加方案的复杂性.

Having the link table is probably a better option. At least it does not violate normalization BCNF (Boyce-Codd normal form). however I would favor being pragmatic. If you have very few of these null values and they are only temporary I think you should skip the link table since it only adds complexity to the scheme.

附注；使用链接表不一定使它成为 n 到 n，如果您在链接表中使用指向您的订单表的外键作为该链接表中的主键，则关系仍然是 1..n.每个订单在该链接表中只能有一个条目.

On a side note; using a link table doesn't necessarily make it n to n, if you in the link table use the foreign key that's pointing to your orders table as the primary key in that link table the relationship is still 1..n. There can only be one entry in that link table per order.

这篇关于可为空的外键不好的做法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！