design question

[anatak]

I am building a site where people can order something.

then the order will be stored in a mysql db.

I would like to track the status of the transaction.
ordered or executed

ordered would mean that the customer ordered something but the transaction has not been processed yet.

executed would mean that the customers transaction has been executed.

 

I wonder what is the best way to do this ?
My initial idea was to have a table orders with an order date (the date / time we receive the order), and execution date (date / time we completed the order) and a boolean field to decide if the order has been executed or not.
 

Any better designs for this ?

 

 

[webbhelp]

I do like this in my webshop.

The order is created in the database with the customers personally information and the products they bought.

The order is create with a field in the database "paid" = 0 / false

When the order is paid then paid will be updated to 1 / true

if the customer changes his mind and go back because he/she wants to buy more products, then when the customer leaves the payment page I look if a SESSION with the order ID exists, if it exists, I remove the order from the database and the session. 

If the customer have changed his products and want to pay again, just another order is created because the other one was removed.

 

BUT

If the customer leaves the payment page without coming back to the shop, then there will be an unpaid order in the database table. The solution there would be to delete unpaid orders where the date is ... let's say 1 week old or something I don't know.

But if the customer will pay with invoice, then it shouldn't be deleted in 2 weeks because maybe the payemnt will arrive later.

[kicken]

I wonder what is the best way to do this ?

My initial idea was to have a table orders with an order date (the date / time we receive the order), and execution date (date / time we completed the order) and a boolean field to decide if the order has been executed or not.

That would work. Rather than a boolean field, I would just make a general status field so that in the future if you want to add additional states (ie, cancelled) you can. You'd have a separate table with a list of possible statuses then the status field in the order table would be a foreign-key reference.