Menu

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

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

Reading 0:00
15s threshold

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

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.