在MySQL表中存储历史价格表的最佳方法是什么？

Basically, my question is this - I have a list of prices, some of which are historical (i.e. I want to be able to search that product X was

Basically, my question is this - I have a list of prices, some of which are historical (i.e. I want to be able to search that product X was $0.99 on March 11, $1.99 on April 1, etc...). What is the best way to store this information?

基本上，我的问题是 - 我有一个价格清单，其中一些是历史价值（即我希望能够搜索产品X在3月11日为0.99美元，4月1日为1.99美元等）。存储此信息的最佳方法是什么？

I assumed I would probably have a Product table that has a foreign key to a price table. I initially thought that storing the current price would probably be the best bet, but I think I want to be able to store historical price data, so would the better route to go be to store a table like the following for the price list:

我假设我可能有一个Product表，它有一个价格表的外键。我最初认为存储当前价格可能是最好的选择，但我认为我希望能够存储历史价格数据，因此更好的方法是在价目表中存储如下表格：

CREATE TABLE prices (
         id BIGINT auto_increment not null,
         primary key (id),
         price DECIMAL(4,2) not null,
         effectiveStartDate DATETIME NOT NULL,
         effectiveEndDate DATETIME 
);

I'm at a bit of a loss here. I'd like to be able to search products efficiently and see how the price of that product changed over time. How can I efficiently associate a set of these prices with a product? I guess what I am asking is, 'What would be the best way to index this in order to be able to provide an efficient search for queries that span a specific set of dates?'

我在这里有点不知所措。我希望能够有效地搜索产品，并了解该产品的价格如何随时间而变化。如何有效地将一组这些价格与产品相关联？我想我要问的是，'为了能够为跨越特定日期的查询提供有效搜索，最好的方法是将其编入索引？'

3 个解决方案

#1

Separate the need for historical data from the need for current price. This means:

将历史数据的需求与当前价格的需求分开。意即：

1) Keep the current price in the products table.

1）将当前价格保留在产品表中。

2) When the price changes, insert the new price into the history table with only the start date. You don't really need the end date because you can get it from the previous row. (You can still put it in, it makes querying easier)

2）当价格变化时，将新价格插入历史表中，仅包含开始日期。您实际上并不需要结束日期，因为您可以从上一行获取结束日期。（你仍然可以把它放进去，它使查询更容易）

Also remember that your order history provides another kind of history, the actual purchases at a given price over time.

另请记住，您的订单历史记录提供了另一种历史记录，即在给定价格下实际购买的历史记录。

#2

First, make sure that you really need to do this. Are you storing orders in the same database? If so, you can always view historical price trends by examining the price of the item in orders over time. This will also allow you to make correlations between price changes and changes in ordering patterns; the only case it wouldn't address is if a price change resulted in no orders being placed.

首先，确保你真的需要这样做。您是否在同一个数据库中存储订单？如果是这样，您可以随时通过检查订单中物品的价格来查看历史价格趋势。这样您还可以在价格变化和订购模式变化之间建立关联;它不会解决的唯一情况是，如果价格变化导致没有下订单。

That being said, if you want an independent record of price changes, what you've presented is good. The only thing I would recommend is eliminating the end date; unless you plan on having a gap in time where the product has no price or overlapping prices, start date is sufficient and will make your logic easier.

话虽这么说，如果你想要一个独立的价格变化记录，你所呈现的是好的。我唯一建议的是取消结束日期;除非您计划在产品没有价格或重叠价格的时间间隔，开始日期就足够了，这将使您的逻辑更容易。

#3

The end date may be viable for more complex system where you can plan prices of product (i.e. various seasonal promotions/etc.) ahead. (oh, this is BS, should have thought more about it ... ok, you need end date only if you plan multiple prices of product at the same time, differentiated by something else ... still it's often convenient to have it inside current record, not looking at previous/next one)

对于更复杂的系统，结束日期可能是可行的，您可以在其中计划产品价格（即各种季节性促销/等）。（哦，这是BS，应该多考虑一下......好吧，只有当你同时计划产品的多个价格时才需要结束日期，区别于其他东西......仍然通常很方便将它放在里面当前记录，不看上一个/下一个）

Actually with most complex systems it is not uncommon to have several current prices differentiated by "dimensions" only (i.e. some kind of attribute which may be then decided by actual shipping place or customer's country, etc...)

实际上，对于大多数复杂系统而言，通过仅仅“维度”区分的几个当前价格（即某种属性可能由实际的运输地点或客户的国家等决定......）并不罕见。

I would also check twice your platform/language/framework/style of work before you omit the custom "id" primary key in favor of [product_id, starting_date,..?..] composite pk. The latter is somewhat more logical choice (at least I personally prefer it), but it may backfire sometimes, for example if your DB library has only limited way to work with more complex primary keys.

在省略自定义“id”主键之前，我还会检查你的平台/语言/框架/工作方式的两倍，以支持[product_id，starting_date，..？..] composite pk。后者在某种程度上更合乎逻辑（至少我个人更喜欢它），但它有时可能会适得其反，例如，如果你的数据库只有有限的方式来处理更复杂的主键。

.99 on March 11,

CREATE TABLE prices (
         id BIGINT auto_increment not null,
         primary key (id),
         price DECIMAL(4,2) not null,
         effectiveStartDate DATETIME NOT NULL,
         effectiveEndDate DATETIME 
);