R – e-shop implementation: Status for Orders


Hello Again my fellow programmers out there,

I'm designing and programming from scratch a online shop. It has a Module to manage "Orders" that are recieved via the frontend.

I'm needing to have a status to know whats happening with an order in s certain moment, let's say the statuses are:

  • Pending Payment
  • Confirmed – Awaiting shipment
  • Shipped
  • Cancelled

My question is a simple one, but is very important to decide on the store design, and is: What would you do so store this status: Would you create a column for it in the Orders table or would you just "calculate" the status of each order depending if payments has been recieved or shipments has been made for every order? (except I suppose for a is_cancelled column)

What would be the best approach to model this kind of problem?

PD: I even wish in the future to have these statuses configurable buy other clientes using the same software..

Best Solution

If you can calculate it, you should calculate it. Otherwise you have redundant data and run the risk of having inconsitencies.

That's not to say you can't add a status column for performance reasons or to make querying easier, but start without it and make sure your authoritative data stays that way. Personally I prefer to stick with the calculate-on-query approach unless I can prove the performance isn't good enough.

Related Question