在MySQL表中存储历史价格表的最佳方法是什么? [英] What is the best way to store a historical price list in a MySQL table?

问题描述

基本上，我的问题是-我有一个价格清单，其中一些是历史价格(即我希望能够搜索到产品X在3月11日价格为0.99美元，在4月1日价格为1.99美元，依此类推...) .存储此信息的最佳方法是什么?

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?

我假设我可能会有一个Product表，该表具有价格表的外键.最初，我认为存储当前价格可能是最好的选择，但我想我希望能够存储历史价格数据，所以最好的存储方式是存储一个类似于以下价格表的表: /p>

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:

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?'

推荐答案

将历史数据的需求与当前价格的需求分开.这意味着:

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

1)将当前价格保存在产品表中.

1) Keep the current price in the products table.

2)价格更改时，将新价格插入带有开始日期的历史记录表中.您实际上不需要结束日期，因为您可以从上一行获取结束日期. (您仍然可以将其放入，这使查询更加容易)

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)

还请记住，您的订单历史记录提供了另一种历史记录，即随着时间的推移以给定价格进行的实际购买.

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

这篇关于在MySQL表中存储历史价格表的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！