e-shop implementation: Status for Orders?

Question

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..

Answer 1

I would store the status against each line in the order. You need to take into account if someone orders 2 things then cancels one of them.

Answer 2

You could do it in two different styles of which I can think at the moment:

Option one:

Create a Table for Orders and insert a Column that says 0,1,2 or 3 depending on the amount of statuses is.

In another table the primary Keys are ascending from 0 to 3 (as above) and the next colum says: status as a varchar or string, depending on your sql server. There you can insert stuff like 'pending'

Option two:

You store it in an extra colum on each order. Depending on the amout of orders, this is the best solution for small shops. With the amount of orders getting bigger this takes more and more time.

The reason why the second solution is slower is that in general selecting strings is slower than selecting integer values.

Greetings

Answer 3

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.

Answer 4

Create a status column for Orders

The rationale is that if Orders can be associated with multiple Payments or Invoices (depending on your implementation), then the status of an Order does not have an absolute relation to any one pending or completed Payment and hence is not directly calculable.

An Order could be marked as Paid or Completed even though no payment has been received for it. An order represents exactly that: a scheduled shipment to a customer. It should not be representative of actual income, which in turn is the domain of your Payments table.

Exact implementation will depend on your workflow; I have accounted "completed" orders as "substitutes" for payments before, but it gets messy. Especially when you run promotions or want to give stock away and have to account for lost stock and no income received. Welcome to Accounting Hell.

When a Payment is received for an order, your business logic should decide whether to adjust the status of the order. If the order was pending and the total received Payments now add up to equal to or more than the order value, the Order should be marked as Paid, indicating that it is ready to be shipped.

To summarise: Completed Payments represent income, whereas Completed Orders represent shipments and/or stock changes.

Answer 5

You would have a status_id column in your orders table, then a separate orders_status table with the ids and status descriptions: 'pending, on hold' etc.

Your payment module upon completion of an order will set the status to pending.

I tend to have two other fields: -

Boolean ispaymentcompleted to indicate whether the order has actually been completed in payment terms and is ready to be processed. This is not an issue if you only save the order details after successful payment.

Then I'll have a isneworder boolean field simply to highlight the new orders in the admin system and also to quickly determine new orders that need to be processed.

PS. You do need to keep track of order status for an order. Calculating on the fly will lead to less flexibility. And this is standard implementation for all the ecommerce systems I have encountered.