Modeling Order Extensions

From: Lars Tackmann <ltackmann_at_gmail.com>
Date: Fri, 17 Oct 2008 04:05:00 -0700 (PDT)
Message-ID: <00a2b094-8c87-4c04-9ce6-3023a7eae05a_at_y79g2000hsa.googlegroups.com>



Hi all

I have a leasing system where customers rent items and pay them of over time. This is somewhat different from the usual order/order_line design since in this case the customer only have one order which is then modified via extensions (i.e. as he extends the leasing periods, lease more/other products...). Consider the following use case:

  1. A customer purchases item I1 at price P1 and item I2 with price P2
  2. This results in order number O1 with order lines that references O1, item I1 and I2 and prices P1 and P2.
  3. The total price is calculated and saved in O1. The price is payed off in 24 monthly payments.

After some time the customer purchases more of Item I1 now priced P3
(the item got cheaper). The question is how do I best model such order
extensions ?. One way could be this:

orders -> orders_version -> order_items

so when the initial order is created we have order O1 with version V1
(referenced in the order_items table which contains products, quantity
and a copy of the current price), later we add version V2 to O1 containing the extra items.

Another possibility would be to freeze price changes (disable updates) and then simply reference the prices directly:

orders -> orders_items -> product_prices

thereby forcing all products to have a collection of prices, with each price being valid for a different period of time.

is there other possibilities for some neat relational design for this problem ?. By the way is there any books that goes through design options for real life database problems such as this ? (similar to Martin Fowlers "Analysis Patterns" book).

Thanks in advance. Received on Fri Oct 17 2008 - 13:05:00 CEST

Original text of this message