在 MySQL 表中存储历史价目表的最佳方式是什么？
基本上，我的问题是这样的 - 我有一个价格列表，其中一些是历史价格（即我希望能够搜索该产品 X 在 3 月 11 日的价格为 0.99 美元，在 4 月 1 日的价格为 1.99 美元，等等...）。存储这些信息的最佳方式是什么？
我假设我可能有一个产品表，其中有一个价格表的外键。我最初认为存储当前价格可能是最好的选择，但我认为我希望能够存储历史价格数据，因此更好的方法是存储如下所示的价格表表
CREATE TABLE prices (
id BIGINT auto_increment not null,
primary key (id),
price DECIMAL(4,2) not null,
effectiveStartDate DATETIME NOT NULL,
effectiveEndDate DATETIME
);
：我在这里有点不知所措。我希望能够有效地搜索产品并了解该产品的价格如何随时间变化。如何有效地将一组这些价格与产品关联起来？我想我要问的是，“索引此内容的最佳方法是什么，以便能够为跨越特定日期集的查询提供有效的搜索？”
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?
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?'
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
将历史数据的需求与当前价格的需求分开。这意味着：
1) 将当前价格保留在产品表中。
2）当价格发生变化时，将新价格插入历史表中，仅包含开始日期。您实际上并不需要结束日期，因为您可以从上一行获取它。 （您仍然可以将其放入，这使查询更容易）
还要记住，您的订单历史记录提供了另一种历史记录，即一段时间内以给定价格的实际购买量。
Separate the need for historical data from the need for current price. This means:
1) Keep the current price in the products table.
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.
首先，请确保您确实需要这样做。您是否将订单存储在同一个数据库中？如果是这样，您始终可以通过检查订单中商品随时间的价格来查看历史价格趋势。这还允许您在价格变化和订购模式变化之间建立关联；它无法解决的唯一情况是价格变化导致没有下订单。
话虽这么说，如果您想要价格变化的独立记录，那么您提供的内容就很好。我唯一建议的是取消结束日期；除非您计划在产品没有价格或价格重叠的时间间隔内，开始日期就足够了，并且会让您的逻辑更容易。
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.
对于更复杂的系统，结束日期可能是可行的，您可以提前计划产品价格（即各种季节性促销/等）。 （哦，这是废话，应该多考虑一下......好吧，只有当您同时计划产品的多种价格时才需要结束日期，并通过其他东西进行区分......仍然将它放在里面通常很方便当前记录，而不是查看上一个/下一个）
实际上，对于大多数复杂的系统，仅通过“尺寸”（即可能由实际发货地点或客户所在国家/地区决定的某种属性）区分多个当前价格并不罕见，等等...）
在您省略自定义“id”主键以支持[product_id，starting_date，..？..]复合pk之前，我还会检查两次您的平台/语言/框架/工作风格。后者在某种程度上是更合乎逻辑的选择（至少我个人更喜欢它），但有时可能会适得其反，例如，如果您的数据库库只有有限的方式来处理更复杂的主键。
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)
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.