Followup: how to model discount on items in a database?

Question

I am building an ecommerce site and would like to offer discounts on certain items for a limited time. I would like to display how much discount we are offering per product. Hence, I need two values per product, original price and the discounted price for the given duration.

This is in followup to an answer for the question I asked

Schema:

• Product

  • productId
  • Name

• ProductPricing

  • productId (FK)
  • startDateTimeStamp
  • endDateTimeStamp
  • price
  • original price only applicable if we use approach A (comes later on)

Data:

Product:
    1   |   Apple
    2   |   Banana

T1: Dec 21, 2011: No deals at this time

ProductPricing
    1   |   Dec 20, 2011, 00:00 |   Jan 1, 2038, 00:00  |   10$ |   10$
    2   |   Dec 20, 2011, 00:00 |   Jan 1, 2038, 00:00  |   20$ |   20$

T2: Dec 24, 2011: Deal! Apply discount of 25% on apples from Dec 25, 14:00 - Dec 26, 14:00

Approach A. - Query updates apple prices for the given duration

ProductPricing
    1   |   Dec 25, 2011, 14:00 |   Dec 26, 2011, 14:00 |   7.5$|   10$
    2   |   Dec 20, 2011, 00:00 |   Dec 25, 2038, 00:00 |   20$ |   20$

Approach B. - Query adds another record with apple prices for the given duration

ProductPricing
    1   |   Dec 20, 2011, 00:00 |   Jan 1, 2038, 00:00  |   10$ |   10$
    2   |   Dec 20, 2011, 00:00 |   Dec 25, 2038, 00:00 |   20$ |   20$
    1   |   Dec 25, 2011, 14:00 |   Dec 26, 2011, 14:00 |   7.5$|   10$

T3: Dec 27, 2011 - Options

Approach A. At this time, the deal is expired, should I reset the endTimeStamp using a trigger ?

Approach B. Should I delete the most recent record for the product for which the deal just expired ?

Answer 1

The design of the ProductPricing table allows us to never have to delete old pricing data (sometimes management wants a report based on that data). With what you have described above, you'd start like this (I changed the starting date just so it's easy to pick out that yes, this was the original price when the system went into place):

ProductPricing
   1   |    Jan 1, 1970, 00:00:00 |   Jan 1, 2038, 00:00:00  |   10$ |   10$

Now let's say you give a discount price on your apples, and you wanted to be proactive and set up the system for when the sale was over:

ProductPricing
   1   |    Jan 1, 1970, 00:00:00 |  Dec 20, 2011, 00:00:00  |   10$ |   10$
   1   |   Dec 20, 2011, 00:00:01 |  Dec 26, 2011, 00:00:00  |  7.5$ |   10$
   1   |   Dec 26, 2011, 00:00:01 |   Jan 1, 2038, 00:00:00  |   10$ |   10$

What we did here was:

1. Update the existing record with the 2038 timestamp, changing the endDateTimeStamp field to reflect the beginning of the sale
2. Insert a new record to define the sale
3. Insert another new record to reflect the normal price again

With no overlapping timestamps, you're guaranteed to get a single record when you query the database for your price. Thus,

SELECT p.Name, pp.price, pp.original_price
FROM Product p
INNER JOIN ProductPricing pp ON pp.productId = p.productId
WHERE NOW() BETWEEN pp.startDateTimeStamp AND pp.endDateTimeStamp

would get you a product list with current pricing.