I've got an app where users can place orders for recurring delivery of a particular service.
orders table has columns
service_id links to the service the user has ordered and
state can be
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
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 18.104.22.168 in case that matters, but I figure this is more of a generic SQL modeling question)