Re: Modeling Order Extensions

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.

There are two ways of looking at your stated problem: 1) temporal data and 2) proper normalization of historical records.

Lorentzos', Date's & Darwen's _Temporal Data and the Relational Model_ give a theory based solution for solving the temporal data problem; however, the solution relies on interval type generators which do not exist yet in any commercial product.

Historical data or audit trails have different functional dependencies from the tables where their values originated at different points in time. It's simply a matter of not declaring constraints that do not hold. Received on Fri Oct 17 2008 - 14:02:36 CEST