Modeling Order Extensions

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.

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