15

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

Community
  • 1
  • 1
brainydexter
  • 19,826
  • 28
  • 77
  • 115

4 Answers4

11

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.

WWW
  • 9,734
  • 1
  • 29
  • 33
  • with a product price table, you might not even need the deal table.. the dates are key. – Randy Dec 20 '11 at 14:09
  • 1
    @Randy: which is why I said "instead of creating a Deals table". ;) – WWW Dec 20 '11 at 14:28
  • @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. – Tom Mac Dec 20 '11 at 14:33
  • 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. – WWW Dec 20 '11 at 15:20
  • @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 Dec 20 '11 at 15:22
  • 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 ? – brainydexter Dec 20 '11 at 15:32
  • @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. – WWW Dec 20 '11 at 15:59
  • 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 ? – brainydexter Dec 20 '11 at 16:15
  • @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. – Green Nov 04 '16 at 14:30
  • 1
    @Green generally, the Orders History has their own price field, instead of relying on the current one of the Product. – jonathancardoso Feb 10 '17 at 00:46
  • @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. – Tycholiz Jan 20 '21 at 00:15
4

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).

socha23
  • 10,171
  • 2
  • 28
  • 25
2

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.

Drago
  • 21
  • 2
0

I would add discountPrice,discountPercentage,endDiscountDate column to table Product and create table discount history to keep discount tracking

Then when select no need to join table you can choose the correct price by checking endDiscountDate

tarn
  • 546
  • 6
  • 10