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

Answer 1

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

Answer 2

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.