Storing Pricing History

1

Hello. I could use some help figuring out this secondary problem in order to to help me figure out my larger Handling Disparate Products problem...

If I want to store the pricing history for a product, would the following design be acceptable...

ONLINE_BOOK --||-------|<-- ONLINE_BOOK_PRICING


ONLINE_BOOK
- id
- name
- current_unit_price


ONLINE_BOOK_PRICING
- id
- online_book_id
- historic_unit_price
- start_date
- end_date

My thinking is that when you add a new price (i.e. price change), you would insert the newest price into the ONLINE_BOOK_PRICING table and you would update ONLINE_BOOK.current_unit_price

Thoughts?

2

Or

ONLINE_BOOK
- id
- name



ONLINE_BOOK_PRICING
- id
- online_book_id
- unit_price
- start_date
- end_date

Current price would be the one with end_date null. @JeffModen recommends a not null column have some default known value in there instead of null but that indicates this is the current price?

3

I can see the benefit of that.

Why is having a NULL in end_date so bad?

In a field like that it doesn't seem like a big deal...

4
5

Uh, no... if you look at the quote, I was talking about "ModifiedBY" columns, which typically have a variable width datatype. If EndDate is based on a fixed width temporal datatype (datetime, date, datetime2()), then you won't have expansive updates because of a null changing to a value in such columns.

I DO, however, recommend that EndDate not be NULL for other reasons and they're the same reasons why they are never null in Temporal Tables (for example). IMHO, EndDate should always be "9999", which resolves to 01 Jan 9999 so that you don't have to test for both NULL and some future date in your queries. The reason why I don't use 12/31/9999 23:59:59.9999999 like MS does for their temporal tables is because that leaves no room for some other "tricks of the trade" on such columns whereas 01/01/9999 does. It's also easier to type "9999" than "12/31/9999 23:59:59.9999999"

1 Like
6

The current price is needed so often it's not a bad idea to include it in the main table (i.e. denormalize it). Selective denormalization is normal when doing physical designs.

2 Likes
7

@ScottPletcher,

That is good to know.

In general, though, is the parent-child relationship I described above an okay way to tracking a changing product price?

I ask, because for another cluster of tables related to membership plans, I took this approach...

MEMBER -||------|<- SUBSCRIPTION ->O------||- MEMBERSHIP_PLAN_PROMOTION ->|------||- MEMBERSHIP_PLAN

8

This is typically a price history table. But you still include the actual price charged at time of sale in the order for historical purposes. You must be absolutely sure you have the correct price for credits, for one thing. Sometimes the salesman/manager can reduce a price, either to close to sale, as an offer to make up for something bad that happened with the customer, etc.. If after such a situation they can return an item, you want to be certain you never credit them more than what they actually paid.

I admit I didn't see the ERD you're referring to, I missed it. So I'll need to review this q further to comment on that.

9

Good reminder! But in then that is the purpose the ORDER_DETAILS table serves - it reflects the "purchase_price" of an item versus PRODUCT.unit_price which will change over time.

The last ERD I posted was new and my point was that in that other "cluster" I approach tracking the price history differently.

In addition to that ERD, I guess you need to know this...

SUBSCRIPTION

  • id
  • member_id
  • membership_plan_promotion_id
  • purchase_price
  • contract_start_date
  • contract_end_date

MEMBERSHIP_PLAN_PROMOTION

  • id
  • membership_plan_id
  • unit_price
  • contract_term
  • start_date
  • end_date

MEMBERSHIP_PLAN

  • id
  • name
  • etc

In that cluster, I put the pricing history in between the SUBSCRIPTION and MEMBERSHIP_PLAN because a MEMBER really subscribes to a particular MEMBERSHIP_PLAN_PROMOTION versus a Membership Plan itself.

Follow me?