如何模拟数据库中项目的折扣？ [英] how to model discount on items in a database?

问题描述

我的产品表（MySQL）如下所示：

我正在建立一个电子商务网站，并希望在有限的时间内为某些项目提供折扣。 p>

 
产品
  -  productId 
  - 名称
  - 重量
  - 价格项目的封面）
 

我应该为另一张交易表：

 
交易
  -  dealID 
  -  productID（Foreign Key）
  - 折扣（分数值：百分比）
  -  description 
 

检索项目：

• q1：查找productID = $ / $>
  
• q2：从交易表中更新折扣价格


• q3：返回所有产品

有更好的方法吗？此外，我如何处理仅在有限时间内交易的情况？

编辑：
我想显示我们提供多少折扣产品。因此，我需要每个产品两个值，原始价格和给定持续时间的折扣价格。

我发布了crontab提出的解决方案的追踪这里

解决方案

您可以考虑添加开始时间戳和结束时间戳到您的交易表。这样，您可以检查以确保当前日期在交易的开始和结束日期之间。

您的交易 table并不真的需要一个 dealID - 它可以用 productID 和开始日期折扣。另外，根据给定项目的价格可能有多高，请记住让您的折扣字段足够精确（像$ code> DECIMAL 12,8 ）。

如果是我，我实际上将价格 产品表，并创建一个 ProductPricing 表，而不是创建一个交易表。 ProductPricing 表将包含 productID 和起始时间戳作为关键字段，然后还有一个结束时间戳表明何时改变了。而且，当然这个项目的价格。

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

解决方案

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.

这篇关于如何模拟数据库中项目的折扣？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！