CODEWITHSUNDEEP
mysqldatabase-designentity-relationship
brainydexter
brainydexter

Reputation: 20346

how to model discount on items in a database?

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:

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

Upvotes: 15

Views: 22501

Answers (5)

Ecki
Ecki

Reputation: 65

It's a pretty old thread, but I think my take would nevertheless be interesting to anyone coming across this in the future.

My idea is a twist on WWW's proposition of using a ProductPricing table. In the end you would decouple the price from the product but instead of using an end date you would use a start date.

This would change the narrative that the current price is always the price with the most recent start date. A discount would then involve creating two entries:

  1. The discounted price with a start date of whenever the discount should start
  2. The normal price with an end date of when the discount should end.

The improvement I see here is that there is never the possibility of having no price (as an end date can be reached and then the price expires when using endDate).

This can be combined with the propositions from WWW in the comments to include the discount value. I modified it to dynamically select the discount unit (currency, percentage or whatever you want). This is what the table could look like of a product that is discounted during December:

price startDate discount discountUnit
10.00 2025-01-01 null null
5.00 2024-12-01 5.00 "$"
10.00 2024-10-01 null null
7.50 2024-09-01 25 "%"

Upvotes: 0

tarn
tarn

Reputation: 556

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

Upvotes: 0

socha23
socha23

Reputation: 10239

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

Upvotes: 6

WWW
WWW

Reputation: 9860

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.

Upvotes: 12

Drago
Drago

Reputation: 21

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.

Upvotes: 2

Related Questions