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.

My Product table (MySQL) looks like this:

Product
    - productId
    - Name
    - Weight
    - Price (price as on the cover of the item)

Should I make another table for deals:

Deals
 - dealID
 - productID (Foreign Key)
 - discount (fractional value: percentage)
 - description

For retrieving items:

• q1: Find all products in products with productID = all the productIDs in deals table
• q2: Update the price with discount from the deals table
• q3: return all the products

Is there a better way to do this ? Also, how do I handle the case of deal existing for only a limited time ?

EDIT: 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.

I posted a followup to the solution proposed by crontab here

Solution 1

You might consider adding a beginning timestamp and ending timestamp to your Deals table. That way, you can check to make sure the current date is between the start and end dates for the deal.

Your Deals table doesn't really need a dealID - it could be keyed with the productID and the start date of the discount. Also, depending on how high the price could be for a given item, remember to make your discount field something sufficiently precise (something like DECIMAL 12,8).

If it were me, I would actually leave price off of the Product table and create a ProductPricing table instead of creating a Deals table. That ProductPricing table would consist of the productID and a starting timestamp as the key fields, then also have an ending timestamp to indicate when that price changed. And, of course, the price of the item.

Solution 2

Another thing to consider would be, how do you model situation when there is no discount for a given item? You might use a Null Object pattern here - basically, when a product is created, you also add a deal on that product with 0% discount and unlimited time. That way, you could simplify your product retrieval logic (no outer joins in query, no if for calculating price).

Solution 3

I would use a decimal for the discount column.

price * discount = $amount off

price - $amount off = price in cart

For limited time you could put in a expiration date column, and only return rows that are not expired.

Comments

[Randy]

with a product price table, you might not even need the deal table.. the dates are key.

[WWW]

@Randy: which is why I said "instead of creating a Deals table". ;)

[Tom Mac]

@Crontab the ProductPricing approach is a nice solution since it has the added benefit of allowing price adjustments to products that have not originated from a deal/promotion (price increases for example). You could even store the reason for the last price change on the ProductPricing table.

[WWW]

Thanks, we've been using it in production here at work for the last 6 months and it's worked well so far. I like the idea of the reason, I might add that to our pricing tables.

[brainydexter]

@Crontab I want to make sure I understand this correctly, ProductPricing with productID as the Foreign key, and start & end timestamp and price are fields of the table. Product with no deal would have regular price with endtimestamp of (assume) 1 year from current date. To apply a % discount, we update this price with the discount and endTimestamp for as long as the deal is valid. Did I miss anything ?

[brainydexter]

Also, 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. I don't understand how will I be able to support that with ProductPricing ?

[WWW]

@brainydexter: In our system, whichever price is the active on has an expiration date in the year 2038. You could always just add another field to the pricing table for the sole purpose of displaying the discount percentage or amount. Don't be afraid of adding informational fields if the end result is that when you need to make a change, you only need to change things in one place.

[brainydexter]

So, once the deal expires, how do you restore the endTimeStamp. Do you have a trigger of some sorts ? Maybe I should start another question or should I edit this question ?

[Green]

@Crontab, Well, and how do you intend to manage history of orders with your ProductPricing that is constantly changes? Your users will get prices in My Orders History anything but what they really had paid. Price must be immutable.

[jonathancardoso]

@Green generally, the Orders History has their own price field, instead of relying on the current one of the Product.

[Tycholiz]

@Crontab why default the active price to expire in 2038, as opposed to just leaving it null? I would think this gives other benefits, such as easily being able to get current price by selecting where end_date is null. Also, I'm curious of brainydexter 's question about restoring the previous price once discount ends.