DB Schema: Versioned price model vs invoice-related data

Question

I am creating some db model for rental invoice generation. The invoice consists of N booking time ranges.

Each booking belongs to a price model. A price model is a set of rules which determine a final price (base price + season price + quantity discout + ...).

That means the final price for the N bookings within an invoice can be a complex calculation, and of course I want to keep track of every aspect of the final price calculation for later review of an invoice.

The problem is, that a price model can change in the future. So upon invoice generation, there are two possibilities:

(a) Never change a price model. Just make it immutable by versioning it and refer to a concrete version from an invoice.
(b) Put all the price information, discounts and extras into the invoice. That would mean alot of data, as an invoice contains N bookings which may be partly in the range of a season price. Basically, I would break down each booking into its days and for each day I would have N rows calculating the base price, discounts and extra fees.

Possible table model:

Invoice
   id: int

InvoiceBooking          # Each booking. One invoice has N bookings
   id: int
   invoiceId: int
   (other data, e.g. guest information)

InvoiceBookingDay       # Days of a booking. Each booking has N days
   id: int
   invoiceBookingId: id
   date: date

InvoiceBookingDayPriceItem   # Concrete discounts, etc. One days has many items
   id: int
   invoiceBookingDayId: int
   price: decimal
   title: string

My question is, which way should I prefer and why.

My considerations:

With solution (a), the invoice would be re-calculated using the price model information each time the data is viewed. I don't like this, as algorithms can change. It does not feel natural for the "read-only" nature of an invoice. Also the version handling of price models is not a trivial task and the user needs to know about the version concept, which adds application complexity.
With solution (b), I generate a bunch of nested data and it adds alot of complexity to the schema.

Which way would you prefer? Am I missing something?

Thank you

score 2 · Answer 1 · answered May 19 '17 at 21:02

There is a third option which I recommend. I call it temporal (time) versioning and the layout of the table is really quite simple. You don't describe your pricing data so I'll just show a simple example.

Table: DailyPricing
ID  EffDate     Price ...
 A  01/01/2015  17.50 ...
 B  01/01/2015  20.00 ...
 C  01/01/2015  22.50 ...
 B  01/01/2016  19.50 ...
 C  07/01/2016  24.00 ...

This shows that all three price schedules (A, B and C just represent whatever method you use to distinguish between price levels) were given a price on Jan 1, 2015. On Jan 1, 2016, the price of plan B was reduced. In July, the price of plan C was increased.

To get the current price of a plan, the query is this:

select  dp.Price
from    DailyPricing dp
where   dp.ID = 'A'
    and dp.Effdate =(
        select  Max( dp2.EffDate )
        from    DailyPricing dp2
        where   dp2.ID = dp.ID
            and dp2.EffDate >= :DateOfInterest);

The DateOfInterest variable would be loaded with the current date/time. This query returns the one price that is currently in effect. In this case, the price set Jan 1, 2015 as that has never changed since taking effect. If the search had been for plan B, the price set on Jan 1, 2016 would have been returned and for plan C, the price set on July 1, 2016. These are the latest prices set for each plan; that is, the current prices.

Such a query would more likely be in a join with probably the invoice table so you could perform the price calculation.

select  ...
from    Invoices i
join    DailyPricing dp
    on  dp.ID = i.ID
    and dp.Effdate =(
        select  Max( dp2.EffDate )
        from    DailyPricing dp2
        where   dp2.ID = dp.ID
            and dp2.EffDate >= i.InvoiceDate )
where   i.ID = 1234;

This is a little more complex than a simple query but you are asking for more complex data (or, rather, a more complex view of the data). However, this calculation is probably only executed once and the final price stored back in to the invoice data or elsewhere.

It would be calculated again only if the customer made some changes or you were going through an audit, rechecking the calculation for accuracy.

Notice something, however, that is subtle but very important. If the query above were being executed for an invoice that had just been created, the InvoiceDate would be the current date and the price returned would be the current price. If, however, the query was being run as a verification on an invoice that was two years old, the InvoiceDate would be two years ago and the price returned would be the price that was in effect two years ago.

In other words, the query to return current data and the query to return past data is the same query.

That is because current data and past data remain in the same table, differentiated only by the date the data takes effect. This, I think, is about the simplest solution to what you want to do.

score 1 · Answer 2 · answered May 19 '17 at 20:45

How about A and B?

It's not best practice to re-calculate any component of an invoice, especially if the component was printed. An invoice and invoice details should be immutable, and you should be able to reproduce it without re-calculating.

If you ever have a problem with figuring out how you got to a certain amount, or if there is a bug in your program, you'll be glad you have the details, especially if the calculations are complex.

Also, it's a good idea to keep a history of your pricing models so you can validate how you got to a certain price. You can make this simple to your users. They don't have to see the history -- but you should record their changes in the history log.

DB Schema: Versioned price model vs invoice-related data

2 Answers2