Re: Modeling Order Extensions

From: <>
Date: Fri, 17 Oct 2008 05:57:20 -0700 (PDT)
Message-ID: <>

On Oct 17, 1:05 pm, Lars Tackmann <> wrote:
> 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.

You make sure to store item price at order item lines at the time where the customer picks a item. That way the price of the item can be printed now and a year after and finally be billed at the price that the customer accepted. Price will change over time but your item price is fixed. A good idea is newer to reopen an order but instead make a new one.
This way the logic will work Received on Fri Oct 17 2008 - 14:57:20 CEST

Original text of this message