数据库设计-条件空值 [英] Database Design - Conditional Null Values

问题描述

我有一个 work_orders 实体，它可以有子工作订单，因此表上有一个 parent_id 。在某些情况下，可以单独或与母公司一起为子订单开票。因此，我还有另一列称为 bill_with_parent 。

I have a work_orders entity, it can have child work orders, so there is a parent_id on the table. There are cases where the child order can be invoiced separately or with the parent. So I have another column called bill_with_parent.

由于 bill_with_parent 列仅适用于子工作订单，因此我将其设置为 NULL 用于非子工作订单，如果是孩子，则为 0 或 1 。

Since that bill_with_parent column only ever applies to child work orders, I've been setting it to NULL for non child work orders, and if it's a child, it's either going to be 0 or 1.

是处理此类问题的正确方法吗？

Is that the proper way to handle something like this?

推荐答案

虽然不一定是错误的。我会摆脱 null 并只允许 0 和 1 。当然，对于父母来说，它总是 0 ，只有一个孩子可以同时拥有 1 或 0 。

While it's not necessarily 'wrong'. I'd get rid of the null and just allow 0 and 1. Of course for parents it's always going to be 0, only a child could have both 1 or 0.

当您允许字段为空时，您将永远不得不为添加保护在所有查询中都为null ，因此我将使用 null 保留，仅用于外部字段键或实际上没有有效值的地方。

When you allow a field to be null you forever have to add protection for the null case in all your queries, so I'd reserve using null for use only with foreign fields keys or places where there literally no valid value.

这篇关于数据库设计-条件空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！