Re: Modeling Order Extensions

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