I've an entity Order.

An order has info on date, client, connect who handled order etc.

The order must also store a condition i.e. differentiate between won orders and lost orders.

The concept is the fact that a person may submit a purchase to the organization, but tend to eventually out.

(As domain info, an order isn't of products. It's a services company that attempts to handle clients and makes offers on whenever they can deliver a purchase and also at what cost etc. Therefore the customer could find a much better burgain and support and prevent the ordering process from the organization).

The organization wants data on won orders and lost orders and also the distinction between a won order along with a lost order is just a few more characteristics e.g. ReasonLost that could be Price or Time.

My real question is, what will be the best representation from the Order?

I believed of utilizing just one table and merely have for that orders won, the ReasonLost as null.

Will it seem sensible to produce separate tables for WonOrder and LostOrder when the difference of those new organizations isn't significant?

What will be the best model with this situation?

Play one table. Add an OrderState Area.

Caveat: If you're doing countless transactions daily, then choices such as this need a lot more attention and analysis.

There's another alternative design which you may consider. Within this alternative you retain another table for that order lost reason and relate it for your order table being an optional 1:1. Observe that this really is effectively an implementation of the supertype/subtype pattern in which the lost order subtype has one additional attribute.

It appears such as this:

ERD

This different may be attractive under the following conditions:

  • You lose very couple of orders.
  • Your order table is not wide enough to carry a lengthy enough lost order reason.
  • Your lost order reason is extremely, very large (even BLOB).
  • You possess an aesthetic objection to maintaining a lost order reason inside your order table.