I've got an app where users can place orders for recurring delivery of a particular service.

The orders table has columns service_id and state, where service_id links to the service the user has ordered and state can be active or inactive, reflecting whether the user currently wants to receive that service or not. Users can toggle orders on and off as they please.

When a user turns an order on or off (active/inactive) I need to track this, and I need to be able to query for whether an order was active or inactive on a given date.

My question is: What's the best way to to model something like this, specifically so that I can easily write a query like "all orders that were active on (date)"?

I've thought about making a orders_states table with order_id, state, and date, where each change create a row with the state that was set and the date it was set, but I'm not 100% sure about how I'd query it to get the past state on an arbitrary date, or if there's a better way to model this.

I'm testing / prototyping on this now, but I'm obviously not a SQL expert and would love some suggestions from the pros here. Thanks!

(I'm using Postgres in case that matters, but I figure this is more of a generic SQL modeling question)


I guess you'll need an order state table. You can create a new row every time the state changes for an order (how you do this is up to you: triggers, SP or on the application layer). That table must have a non-null timestamp column to get the time the row was created.

Not the answer you're looking for? Browse other questions tagged or ask your own question.