CODEWITHSUNDEEP
phpdatabasedatabase-designe-commerceshopping-cart
user622378
user622378

Reputation: 2346

Order status and status log

I am developing a backend of e-commerce (online shopping) in PHP / MYSQL.

I want to know what the best way dealing with orders status and status tracking/date.

When a user placed an order, the order status will be 1 (tbl_order.status = 1) on the tbl_order table. Is this the best way?

Here are the numbers of order status:

When staff login on the backend, they can change the order status step by step.

I want to track who done it and the time, how can that be done?

Upvotes: 0

Views: 3260

Answers (2)

mkilmanas
mkilmanas

Reputation: 3485

Current order status should be stored on the order table as you suggest.

But for tracking changes, I would suggest a separate log table, where you would log timestamp, order_id, user_id, old_status, new_status. This way you can retrace full history at any time (as opposed to LastUpdate concept).

This concept can be generalized beyond the order status - any field value changes can be tracked this way (though with lots of tracking log tables tend to grow in size rapidly)

Upvotes: 1

m.edmondson
m.edmondson

Reputation: 30922

There is nothing fundmentally wrong with this method. Will you have an OrderStatus table that stores the numbers next to their meanings? I would recommend this as it will allow for different statuses to be added in the future with a minimum of fuss.

With regards to tracking changes and time - you would usually have a "LastUpdated" and "UserId" column which is updated each time the order is modified (perhaps using a stored procedure to enforce this). I'd put these in tbl_order table so that any changes to the order are logged.

Overall I think you're going down the right path.

Upvotes: 0

Related Questions