如何对数据库中的商品进行折扣建模？
我正在建立一个电子商务网站，并希望在有限的时间内为某些商品提供折扣。
我的产品表（MySQL）看起来像这样：
Product
- productId
- Name
- Weight
- Price (price as on the cover of the item)
我应该为交易创建另一个表：
Deals - dealID - productID (Foreign Key) - discount (fractional value: percentage) - description
用于检索项目：
- q1：查找产品中的所有产品，其productID = 交易表中的所有产品ID
- q2：使用交易表中的折扣更新价格
- q3 ：退回所有产品
有更好的方法吗？另外，如果交易仅在有限时间内存在，我该如何处理？
编辑： 我想显示我们为每种产品提供多少折扣。因此，我需要每个产品有两个值：原始价格和给定持续时间内的折扣价格。
我在此处发布了 crontab 提出的解决方案的后续内容
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您可以考虑向
Deals表添加开始时间戳和结束时间戳。这样，您可以检查以确保当前日期位于交易的开始日期和结束日期之间。您的
Deals表实际上并不需要dealID- 它可以使用productID和折扣开始日期作为键控。另外，根据给定商品的价格有多高，请记住使您的discount字段足够精确（例如DECIMAL 12,8）。如果是我，我实际上会将价格保留在
Product表中，并创建一个ProductPricing表，而不是创建Deals< /代码>表。该ProductPricing表将包含productID和作为关键字段的开始时间戳，然后还有一个结束时间戳来指示价格何时发生变化。当然，还有商品的价格。You might consider adding a beginning timestamp and ending timestamp to your
Dealstable. That way, you can check to make sure the current date is between the start and end dates for the deal.Your
Dealstable doesn't really need adealID- it could be keyed with theproductIDand the start date of the discount. Also, depending on how high the price could be for a given item, remember to make yourdiscountfield something sufficiently precise (something likeDECIMAL 12,8).If it were me, I would actually leave price off of the
Producttable and create aProductPricingtable instead of creating aDealstable. ThatProductPricingtable would consist of theproductIDand 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.另一件需要考虑的事情是，当给定商品没有折扣时，如何对情况进行建模？您可以在此处使用 空对象 模式 - 基本上，在创建产品时，您还可以添加该产品以 0% 折扣且无时间限制。这样，您就可以简化产品检索逻辑（查询中没有外部联接，没有用于计算价格的
if）。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
iffor calculating price).我会在折扣列中使用小数。
价格 * 折扣 = $ 折扣金额
- $ 折扣金额 = 购物车中的价格
在有限的时间内，您可以放入到期日期列，并且仅返回未到期的行。
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.
我会将discountPrice、discountPercentage、endDiscountDate 列添加到表Product 中
并创建表折扣历史记录以保持折扣跟踪
然后当选择不需要加入表时您可以选择正确的价格
通过检查 endDiscountDate
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
这是一个相当古老的话题，但我认为我的观点对于将来遇到这个问题的任何人来说仍然会很有趣。
我的想法是对 WWW 的使用 ProductPricing 表的主张进行了修改。最后，您会将价格与产品脱钩，但不使用结束日期，而是使用开始日期。
这将改变当前价格始终是最近开始日期的价格的说法。折扣将涉及创建两个条目：
我在这里看到的改进是永远不可能没有价格（因为可以到达结束日期，然后使用 endDate 时价格就会过期）。
这可以与评论中 WWW 的建议结合起来以包含折扣值。我将其修改为动态选择折扣单位（货币、百分比或任何你想要的）。 该表可能如下所示：
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:
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: