I have an entity Order.
我有一个实体订单。
The order has information on date, client, associate who handled order etc.
该订单包含日期、客户、处理订单的相关信息等。
Now the order also needs to store a state i.e. differentiate between won orders and lost orders.
现在订单还需要存储一个状态，即区分赢得的订单和丢失的订单。
The idea is that a customer may submit an order to the company, but could eventually back out.
其想法是，客户可以向公司提交订单，但最终可能会退出。
(As domain info, the order is not of items. It is a services company that tries to handle clients and makes offers on when they can deliver an order and at what price etc. So the customer may find a better burgain and back up and stop the ordering process from the company).
(作为领域信息，订单不是项目。这是一家服务公司，它试图处理客户，并在他们什么时候可以交付订单，以什么价格等做出报价。因此客户可能会找到更好的负担，并备份并停止公司的订购过程)。
The company wants data on both won orders and lost orders and the difference between a won order and a lost order is just a couple of more attributes e.g. ReasonLost which could be Price or Time.
该公司希望获得的数据都能获得订单和订单的损失，而赢得订单和订单丢失之间的区别只是一些更多的属性，比如价格和时间。
My question is, what would be the best representation of the Order?
我的问题是，订单的最佳表示是什么?
I was thinking of using a single table and just have for the orders won, the ReasonLost as null.
我在考虑使用一个表，只是为了获得订单，这个理由是空的。
Does it make sense to create separate tables for WonOrder and LostOrder if the difference of these new entities is not significant?
如果这些新实体的差异不显著，那么为WonOrder和LostOrder创建单独的表有意义吗?
What would be the best model for this case?
对于这种情况，最好的模型是什么?
2 个解决方案
#1
2
Use one table. Add an OrderState Field.
使用一个表。添加一个OrderState字段。
Caveat: If you are doing millions of transactions per day, then decisions like this need much more attention and analysis.
注意:如果你每天要处理数百万个事务，那么像这样的决策需要更多的关注和分析。
#2
1
There is another alternative design that you might consider. In this alternative you keep a second table for the order lost reason and relate it to your order table as an optional 1:1. Note that this is effectively an implementation of a supertype/subtype pattern where the lost order subtype has one additional attribute.
你还可以考虑另一种替代设计。在这个替代方案中，由于订单丢失的原因，您保留了第二个表，并将它作为可选的1:1关联到您的订单表。注意，这实际上是超类型/子类型模式的实现，其中丢失的order子类型有一个附加属性。
It looks like this:
它看起来像这样:
This alternative might be attractive under any of the following circumstances:
在下列任何情况下，这种选择可能具有吸引力:
- You lose very few orders.
- 你失去的订单很少。
- Your order table isn't wide enough to hold a long enough lost order reason.
- 你的订单量不够大，无法维持足够长的订单原因。
- Your lost order reason is very, very big (even BLOB).
- 你失去的顺序是非常非常大的(甚至是一团)。
- You have an aesthetic objection to maintaining a lost order reason in your order table.
- 你有一种美学上的反对意见，就是在你的订单中维护一个丢失的订单。
#1
2
Use one table. Add an OrderState Field.
使用一个表。添加一个OrderState字段。
Caveat: If you are doing millions of transactions per day, then decisions like this need much more attention and analysis.
注意:如果你每天要处理数百万个事务，那么像这样的决策需要更多的关注和分析。
#2
1
There is another alternative design that you might consider. In this alternative you keep a second table for the order lost reason and relate it to your order table as an optional 1:1. Note that this is effectively an implementation of a supertype/subtype pattern where the lost order subtype has one additional attribute.
你还可以考虑另一种替代设计。在这个替代方案中，由于订单丢失的原因，您保留了第二个表，并将它作为可选的1:1关联到您的订单表。注意，这实际上是超类型/子类型模式的实现，其中丢失的order子类型有一个附加属性。
It looks like this:
它看起来像这样:
This alternative might be attractive under any of the following circumstances:
在下列任何情况下，这种选择可能具有吸引力:
- You lose very few orders.
- 你失去的订单很少。
- Your order table isn't wide enough to hold a long enough lost order reason.
- 你的订单量不够大，无法维持足够长的订单原因。
- Your lost order reason is very, very big (even BLOB).
- 你失去的顺序是非常非常大的(甚至是一团)。
- You have an aesthetic objection to maintaining a lost order reason in your order table.
- 你有一种美学上的反对意见，就是在你的订单中维护一个丢失的订单。