Modeling Order Extensions

#1

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.

#2

Lars Tackmann wrote:

Quote:

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.

#3

Lars Tackmann wrote:

Quote:

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.

#4

Lars Tackmann wrote:

Quote:

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.

#5

Lars Tackmann wrote:

Quote:

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.

#6

Lars Tackmann wrote:

Quote:

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.

#7

Lars Tackmann wrote:

Quote:

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.

#8

Lars Tackmann wrote:

Quote:

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.

#9

Lars Tackmann wrote:

Quote:

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.

#10

Lars Tackmann wrote:

Quote:

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.