Hi -
I have a fairly simple design question that I'd like some input on. I
have a table called "Orders" which, along with various order
information, contains an "OrderState" column. OrderState can contain
one of the following values:
1 - Order is in preview mode and has not been committed yet. The user
may still make changes.
2 - Order is submitted and will be picked up by a queue service to
process the order and send it to the vendor.
3 - Order has been submitted to the vendor successfully.
4 - Order was processed, but there was some sort of error and we have
canceled the order.
As you can imagine, my application for the most part will just deal
with order states 1 and 2. If an order is in state 3 or 4, it's
pretty much just "archived" information and the only time it would be
needed is if the user wanted to view their previous orders or
something.
It has occurred to me that there might be some advantages of creating
a separate table called "OrderArchive" which would be used to store
order states 3 or 4. This would allow me to get rid of an index on
order state as well as probably use different caching techniques on
each table. It would also keep the Orders table super light and fast
with only very volatile information.
Assuming this theory is correct, what's the best way to design this?
Should I create an OrderArchive table that "derives" from "Orders"
(since the schemas are exactly the same) - I've never done this, only
read about it. Also, I was thinking about how to "move" a row between
tables. The obvious way would be to write a function that inserts the
row into OrderArchive with the same values and the final state, then
deletes the row from the original Orders table (under a single
transaction of course). However, I was thinking it might be nice to
have the DB just "manage" this for me. I could create a trigger on
Orders and the second the OrderState changed to 3 or 4, move the row.
This would have the advantage of delegating this logic entirely to the
DB layer and keeping the app out of the order archiving business.
Any opinions on this subject? Thanks!
Mike
