[SOLVED] How to capture state of an entity in Database during the course of business process?

How to capture state of an entity in Database during the course of business process?

I'm asking those who were already dealing with this situation to share their experience.

Use case :

A business Order comes to a system > becomes active > order is accepted or expired > pending for processing > fulfilled either successfully or not.

You see ? There are at least 4 states of the entity. What I'm doing is that I have a main Table "Order" and then four other tables that contain only orderIds (active, expired, pending, fulfilled) and I'm doing JOINS when querying for orders in different states.

This way the huge table Order is being only read but not written to, so that it is very effective from the performance point of view...

What are your techniques for this use case ?

Solution

You are right that this use case needs additional table(s) because of the performance (writes elimination). But having four tables could cause a lot of maintenance troubles in following iterations.

I'd probably create only a OrderStatusCode table (orderId, statusId) and you can do JOINS like

select * from order 
   inner join activeorder 
      on 
   order.orderID = activeorder.ActiveOrderID 
      WHERE activeorder.status = 'l'

But I sense that as to the entire business cycle there won't be more then 6-10 changes of the entity state. That shouldn't be problem for having everything in one table. If you have a reasonable index on status_id for "where status_id = x" selection. But on the other hand, having extra table makes it easy to add some additional properties regarding status code.