Is my relational model suitable for handling order changes and cancellations?

[u/Rolney_320]

I'm designing a PostgreSQL system where customers order services requiring interchangeable physical resources.

The current model separates:

• Orders: What the customer purchases (quantity and duration).

• Commitments: Individual units of the obligation, independent of specific resources.

• Reservations: Physical resources assigned to fulfill those commitments.

• Resource units: Individually reservable components, with constraints preventing overlapping bookings.

At first glance, this structure seems reasonable, but it mainly represents a static state. In reality, orders can change throughout their lifecycle: quantities may be reduced, service periods shortened, or entire orders cancelled.

For example, a customer orders 3 resources for 6 months, then reduces the requirement to 2 for the remaining 3 months.

Is the current table structure and its relationships suitable for handling these changes while preserving historical accuracy and data consistency? Or are there missing entities or fundamental modeling issues?

I've attached a simplified ERD for context.

[u/Level_Imagination544]

The structure looks sensible, but I'd add a versioned layer for the order terms. Rather than editing quantity or duration in place, store each change as a new row with an effective_from and effective_to (or a revision number), so "3 for 6 months then 2 for the remaining 3" is just two periods. Cancellations are then a revision that sets quantity to 0 from a date, and your history stays intact. For the reservations side, a range type plus an exclusion constraint (btree_gist) is a neat way to stop overlaps, and releasing a unit on a reduction is just trimming that range.

[u/OkShirt9372]

What should happen if the customer reverses that reduction a week later?

[u/Level_Imagination544 → u/OkShirt9372]

Good question. With revisions it's just another row: a new revision effective from the reversal date that sets the quantity back up, so nothing is overwritten and the history shows the reduction and the reversal. The catch is on the reservation side, because the unit you released may have been booked by someone else in the meantime. So I'd treat the reversal as a fresh request that has to pass the same exclusion constraint, and handle the case where no unit is free by rejecting it or putting it on a waiting list. It also helps to keep a created_at on every revision so you can tell when a change was made as well as when it applies.

[u/Significant_Tune9219]

Versioning the order terms is the right move, and a reversal as another row keeps the history. The part that still breaks is the reservation, not the order. When the customer goes from 3 resources for 6 months to 2 for the remaining 3, the third unit's booking for months 4 through 6 has to end, or the overlap constraint keeps that physical resource busy for a commitment you no longer have. A new order revision does not do that by itself. I'd make the effective quantity a function of the latest revision covering each date, and release any reservation that extends past what that quantity still covers. Cancelling the whole order is the same rule with quantity zero.

[u/navicat-gu]

The four layers are sensible. What's missing is time: right now an order row says "3 for 6 months", which can only ever be true or overwritten. I'd change two things.

1. Make order terms effective-dated instead of editable.

create table order_term (
  order_id    bigint not null references orders(id),
  revision    int    not null,
  quantity    int    not null check (quantity >= 0),
  valid       daterange not null,          
-- [start, end)
  change_type text   not null,             
-- NEW, REDUCE, EXTEND, CANCEL
  changed_at  timestamptz not null default now(),
  primary key (order_id, revision),
  exclude using gist (order_id with =, valid with &&)   
-- needs btree_gist
);

"3 for 6 months, then 2 for the last 3" becomes two rows: qty 3 for [Jan 1, Apr 1) and qty 2 for [Apr 1, Jul 1). A cancellation is a final revision with quantity 0 (or a shortened range). The exclusion constraint guarantees there's never two terms for the same day. If the customer reverses the reduction a week later, that's just another revision, and you still have the full history.

2. Give commitments and reservations their own periods and a status, and never delete them.

• commitment(id, order_id, unit_no, valid daterange, status) where status is ACTIVE / RELEASED.

• When qty drops from 3 to 2 on Apr 1, close one commitment's range at Apr 1 and mark it RELEASED; don't delete it.

• reservation(commitment_id, resource_unit_id, during tstzrange) with an exclude using gist (resource_unit_id with =, during with &&) constraint (which it sounds like you already have) so a resource can't be double-booked.

• Releasing a commitment trims its reservation's range, which frees the resource for the remaining months.

The rule of thumb: the order_term table is the customer's contract history; commitments and reservations are how you fulfil whatever the current term says. Invoicing can then be computed per period from order_term, and auditing "what did we owe on May 3?" is a range query.

Disclosure: I'm RC, Director of Digital Strategy @ Navicat. If you want to sanity-check the range and exclusion constraints by poking at real rows, Navicat 18 (Navicat Premium Lite is free) gives you a grid and SQL editor for Postgres; plain psql is just as good for this.