What is the most efficient way to manage order satus

I am designing a small system that manages orders (sort of). These are the statuses that an order can have at any one time:

new - booked less than 24 hours
pending - payment to be received before processing
awaiting collection - ready and wating to be collected
cancelled - normally by customer
suspended - temporary hold
completed - collected by customer
stopped - by organization for fraudulent reasons

The two solutions i have for the oders table are:

To have just one order table and have a field which content i would set to any of the above values depending, then periodically have a routine to move completed, cancelled and stopped entries to their respective history tables.
Then provide a management program for dealing with awaiting collection, suspended and pending orders as per business rules.


Have seperate tables for each of these order statuses and move the details from one table to the order as soon as the status changes occurs and the have coresponding history tables for each table.

Which of these ideas is most efficient performance wise. If none of the above can one suggest an alternative?

no, not if the table is properly optimized

Thanks r937

With the first option if i don’t purge the database by moving old data to a historical file wouldn’t performance degrade with the size of the table and increase in users?

What will be your solution?


most efficient would be if you didn’t move the row to another table at all


thanks. i guess i need to read on optimising database tables now then.