跟进：如何模拟数据库中项目的折扣？ [英] Followup: 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. 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.

这是跟随一个answer 为问题我问

模式：

• 产品

• Product

• productId


• 名称

ProductPricing

ProductPricing

• productId（FK）


• startDateTimeStamp


• endDateTimeStamp


• 价格


• 原价仅适用于我们使用方法A（来

• productId (FK)
• startDateTimeStamp
• endDateTimeStamp
• price
• original price only applicable if we use approach A (comes later on)

数据：

Product:
    1   |   Apple
    2   |   Banana

T1：2011年12月21日：此时没有交易

ProductPricing
    1   |   Dec 20, 2011, 00:00 |   Jan 1, 2038, 00:00  |   10$ |   10$
    2   |   Dec 20, 2011, 00:00 |   Jan 1, 2038, 00:00  |   20$ |   20$

T2：2011年12月24日：交易！从12月25日14:00 - 12月26日14:00，向苹果申请25％的折扣

T2: Dec 24, 2011: Deal! Apply discount of 25% on apples from Dec 25, 14:00 - Dec 26, 14:00

方法A。
- 查询给定持续时间的苹果价格

Approach A. - Query updates apple prices for the given duration

ProductPricing
    1   |   Dec 25, 2011, 14:00 |   Dec 26, 2011, 14:00 |   7.5$|   10$
    2   |   Dec 20, 2011, 00:00 |   Dec 25, 2038, 00:00 |   20$ |   20$

方法B。
- 查询添加另一条记录苹果价格在给定的持续时间

Approach B. - Query adds another record with apple prices for the given duration

ProductPricing
    1   |   Dec 20, 2011, 00:00 |   Jan 1, 2038, 00:00  |   10$ |   10$
    2   |   Dec 20, 2011, 00:00 |   Dec 25, 2038, 00:00 |   20$ |   20$
    1   |   Dec 25, 2011, 14:00 |   Dec 26, 2011, 14:00 |   7.5$|   10$

T3：2011年12月27日 - 选项

方法A。
此时，交易已过期，是否应该使用触发器重置endTimeStamp？

Approach A. At this time, the deal is expired, should I reset the endTimeStamp using a trigger ?

方法B。
我应该删除交易刚过的产品的最新记录吗？

Approach B. Should I delete the most recent record for the product for which the deal just expired ?

推荐答案

ProductPricing 表的设计使我们无需删除旧的定价数据（有时管理层需要基于报表对该数据）。有了你上面所描述的，你会这样开始（我改变了开始日期，所以很容易选出是的，这是系统到位时的原始价格）：

The design of the ProductPricing table allows us to never have to delete old pricing data (sometimes management wants a report based on that data). With what you have described above, you'd start like this (I changed the starting date just so it's easy to pick out that yes, this was the original price when the system went into place):

ProductPricing
   1   |    Jan 1, 1970, 00:00:00 |   Jan 1, 2038, 00:00:00  |   10$ |   10$

现在让我们假定你的苹果有折扣价，你想要主动和在销售结束时设置系统：

Now let's say you give a discount price on your apples, and you wanted to be proactive and set up the system for when the sale was over:

ProductPricing
   1   |    Jan 1, 1970, 00:00:00 |  Dec 20, 2011, 00:00:00  |   10$ |   10$
   1   |   Dec 20, 2011, 00:00:01 |  Dec 26, 2011, 00:00:00  |  7.5$ |   10$
   1   |   Dec 26, 2011, 00:00:01 |   Jan 1, 2038, 00:00:00  |   10$ |   10$

我们在这里做的是：

1. 使用2038时间戳更新现有记录，更改 endDateTimeStamp 字段以反映销售开始


2. 插入新记录以定义销售


3. 再次插入另一个新记录以反映正常价格

1. Update the existing record with the 2038 timestamp, changing the endDateTimeStamp field to reflect the beginning of the sale
2. Insert a new record to define the sale
3. Insert another new record to reflect the normal price again

没有重叠的时间戳，当您以价格查询数据库时，您可以保证获得单个记录。因此，

With no overlapping timestamps, you're guaranteed to get a single record when you query the database for your price. Thus,

SELECT p.Name, pp.price, pp.original_price
FROM Product p
INNER JOIN ProductPricing pp ON pp.productId = p.productId
WHERE NOW() BETWEEN pp.startDateTimeStamp AND pp.endDateTimeStamp

将为您提供目前价格的产品列表。

would get you a product list with current pricing.

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