如何在数据库中对打开/关闭状态进行建模？
假设我有一个 Orders 表，其中包含 OrderID (PK)、CustomerID、CustomerOrderN 等列。 现在我需要添加“关闭”订单的可能性，并指定关闭订单的原因（例如“提供的价格对客户来说太高”、“不可用”、“客户要求关闭订单”）。
问题 1. 在数据库设计中实现这一点的最佳且正确的方法是什么？
我认为最好的方法是创建 Closed 列，该列可以为空（如果订单打开），如果不为空（即如果订单关闭），则该值指向另一个表 OrderCloseReasons。
问题 2. 如果我已经在 Orders 表中拥有一个布尔列 Closed，现在我需要实现指定关闭原因的可能性，该怎么办？ 我不能重构太多，因为系统已经不小了，所以很难重构数据库方案。 在这种情况下，增加指定关闭原因的可能性的最佳方法是什么？
我认为如果我只是将 CloseReasonID 列添加到 Orders 表中，效果会不好。 但我不确定。
先感谢您。
Imagine I have an Orders table with columns OrderID (PK), CustomerID, CustomerOrderN and so on. Now I need to add a possibility to "close" orders with specifying a reason why order is closed (for example "offered price is too high for customer", "not available", "customer asked to close the order").
Question 1. What would be the best and correct way to implement this in database design?
I think the best way is to create Closed column which can be null (if order is open) and if not null (i.e. if order is closed) then the value points to another table OrderCloseReasons.
Question 2. What if I already have :) a boolean column Closed in Orders table, and now I need to implement possibility to specify reasons of closing. I can't refactor much because the system is not so small already, so it's hard to refactor the database scheme. What would be the best way to add possibility to specify reasons of closing in such a case?
I think that if I just add CloseReasonID column to Orders table it will not be good. But I am not sure.
Thank you in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
如果您有一堆想要使用的特定关闭原因，并且需要能够基于特定类型的关闭原因执行查询（例如通过原因 X 获取所有内容），那么您的建议是一个很好的选择idea - null，或关闭原因 ID。
另一方面，如果您不需要搜索等，您可以简单地关闭一列，并另一列描述其关闭原因。
If you have a bunch of specific close reasons that you would want to use, and if you need to be able to perform queries based on a specific type of close reason (say get all by reason X), then what you suggest is a good idea - null, or a close reason ID.
On the other hand, if you don't need searching etc., you could simply have a column closed, and another column which describes why it was closed.
我建议使用 StatusCode 列（可能是 int 数据类型）和一个包含 StatusCode (int) 和 StatusCodeDescription (varchar) 的单独表。 如果您或您的最终用户稍后想到其他可能的状态，这将为您提供更大的灵活性。
I would recommend a StatusCode column (probably int datatype) and a seperate table containing a StatusCode (int) and StatusCodeDescription (varchar). That gives you more flexibility if you or your end users think of another possible status later.
就我个人而言，我会按照您的建议进行查找表，但将其称为“状态”。 我会将 Orders 表中 Status 表的外键设置为 int，不为 null，默认值为 1。
然后 Status 表中的记录将是 (1)Open，(2)Closed Reason 1，(3)关闭原因二等。这样您就可以映射到更高层中的枚举，而无需在存储过程中执行任何特殊操作。 也就是说，您所做的就是在 SELECT 中包含 StatusID，而不必将 null 视为一件事，而将查找值视为另一件事。
Personally, I would do the lookup table as you suggest, but call it Status. I would make the foreign key to the Status table in the Orders table be an int, not null with a default of 1.
Then the records in the Status table would be (1)Open, (2)Closed reason one, (3)Closed reason two, etc. That way you can map to an enum in a higher layer without having to do anything special in your stored procedures. That is, all you do is include the StatusID in your SELECT instead of having to mess around with handling the null as meaning one thing and the lookup values as another.
将 null 和 Reason 合并到一个可为 null 的文本列中并不是一个好主意，因为其他程序员甚至几天后您可能都无法轻松读取它。
有两列，一列是 David 指定的布尔值或状态代码，另一列是原因。 这为您的设计提供了更多的可读性。
但我会向前迈出一步，财务相关软件的最佳实践是内置审计跟踪，因为我当然想知道……
“谁关闭了订单？”
“几点关门？”
“重新开放了吗？”
审计跟踪通常由另一个表组成，例如
OrderAudit
-> 审计ID
-> 订单ID
-> 改变者
-> 更改日期时间
-> 更改列
-> ChangeValue
这将完全控制谁在何时对该订单执行了什么操作。
Merging null and reason into one nullable text column is not good idea because it may not be easily readable by other programmers or even by you after few days.
Having two columns one either boolean or status code as specified by David, and seperate column for reason. This gives more readability to your design.
But I will go one step ahead and best practice in finance related software is to have built in audit trail, because I would certainly like to know..
"Who closed the order?"
"What time it was closed?"
"Was it reopened?"
The audit trail usually consists of another table for example,
OrderAudit
-> AuditID
-> OrderID
-> ChangeMadeBy
-> ChangeDateTime
-> ChangeColumn
-> ChangeValue
Which will give full control over who did what with this order and when.
另一个表仅保存那些已关闭订单的关闭原因。
它不会违反 1NF，因为您不会在数据库模式中引入空值，并且您有最绝对的保证您的更改不会影响现有内容（您明确指出这是本例中的一个主要问题）。
编辑
参见数据库日期中的“第一范式真正意味着什么”：2000-2006 年著作（Springer-Verlag，2006 年）。 也可能作为独立论文在互联网上找到。
以及来自“数据库系统简介”，第 8 版。 ：“当且仅当在该相关变量的每个合法值中，每个元组的每个属性都包含一个值时，相关变量才属于 1NF。” （并且 null 不可能是一个值，因为它不等于其自身）。
An additional table holding the close reason for just those orders that are closed.
It doesn't violate 1NF because you don't introduce nulls in the database schema, and you have the most absolute guarantee that your changes won't affect existing stuff (which you clearly indicated is a major concern in this case).
EDIT
See e.g. "What First Normal Form Really Means" in Date on Database: Writings 2000-2006 (Springer-Verlag, 2006). Possibly also findable as a standalone paper on the internet.
And from "Introduction to database systems", 8ed. : "A relvar is in 1NF if and only if, in every legal value of that relvar, every tuple contains exactly one value for each attribute." (and null cannot possibly be a value, because it is not equal to itself).