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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
