so we have a table orders

with say:

id, product_id, order_state_id, comment

1, 5, 2, 'thisisatest'

we have a table order_state with

id, name

1, pending processing

2, payment accepted

for each of the order states of an order, i must save a date as of when the state was performed. The order can only be in one state at a time.

say we have an order that gets put into payment accepted state. Now I have to save a date/time along with it.

what is the cleanest / best way of doing that?

1 Answer 1


Since you already put order_state_id in your orders table why you don't put a timestamp or a datetime column along with it?

When you update order_state_id to the desired state update the timestamp along with it.

If you think that there is a high probability that your status list will expand over time then you may have a need to keep history of status changes for each order. In order to do that you'll need to throw out order_state_id and the timestamp from orders and create an association table like that will link orders with order_state :

record_id (optional)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.