1

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?

What would be the best model for this case?

Cratylus
  • 52,998
  • 69
  • 209
  • 339

2 Answers2

2

Use one table. Add an OrderState Field.

Caveat: If you are doing millions of transactions per day, then decisions like this need much more attention and analysis.

Steve Wellens
  • 20,506
  • 2
  • 28
  • 69
  • Ok. But how would it defer depending on the ammount of transactions per day? Even in this case, a single table would be faster, i.e. no `JOIN`.Unless I am misunderstanding the `Caveat` part of you answer – Cratylus Oct 01 '11 at 11:54
  • The larger the system, the more careful we must tread. Retrieving single table data is faster than joining, but with large systems, conserving diskspace can also be an issue, reporting/datamining can be an issue, table security can be an issue, etc., etc., etc. Caveat on the Caveat: In general, I've seen more problems caused by over-engineering than under-engineering. – Steve Wellens Oct 01 '11 at 13:11
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.

It looks like this:

ERD

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.
Joel Brown
  • 14,123
  • 4
  • 52
  • 64