A lightweight order history table with order state and date/time of change would perhaps make it easy to list the history of the order, but if you want to find orders at a certain stage or above, such queries get more complicated with such a separate history, than if having all date fields in the order table. Especially if a state could also be skipped. You end up with EXISTS queries or such things. The advantage of course is, you can easily extend further individual states, but do you need that? If your workflow often changes such a structure would help datawise, but alongside of that you'd need application changes, too, most probably.
What happens if an important state is missing? That's perhaps an easy case, comparing to a NULL value in the structure you have now, but what about double states in the separated table? Which date is correct, then? Of course you can only have one date in your order table structure now, and if a date would be set double, you would find the last stored date in it. The order history would perhaps reveal such things you don't detect with a single field for that data, which may be good to know, but it may also cause new conflicts.
So overall it may cause more trouble than it's worth and it's only a good consideration, if you need more flexibility, eg many different workflows with the orders, if they are partly physical products, partly services, lending, or also downloads and you offer all that, eg you are amazon, then this would make sense.
Bye, Olaf.
