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?…