如何处理发票申请中的价格波动？
在发票应用程序中，请考虑以下情况：
我有一个产品表，其中还包含产品的价格。然后我有一个发票和发票行表，在每个发票行中，我引用产品 ID 和数量。在这种情况下，我不会将产品的价格与发票行一起存储。
现在几个月后，如果产品的价格发生变化，任何报告都会显示基于当前价格的销售量，而不是产品实际销售的价格。
我想到的一个解决方案是，我们保留一个名为“价格”的单独表，该表链接到每个产品，每当产品价格发生更改时，都会将一条记录插入到该价格表中。该表中的最新记录始终被视为新发票的当前价格。每个发票行通过指示价格 ID 来指示其价格，以便稍后在报告中正确显示。
处理此类情况的最佳做法是什么？
In an invoicing application, consider following:
I have a products table that also contains the price of the product. And then I have an invoice and invoice_lines table and in each invoice line, I refer to the product id along with quantity. In this case, I am not storing the price of the product with the invoice line.
Now few months later, if the price of the product changes, any report would show the volume of the sales based on current price instead of the price on which the product was actually sold.
One solution that comes to my mind is that we keep a separate table named prices that links to each product and whenever the price of the product is changed a record is inserted into this prices table. The latest record in this table always is considered as the current price for new invoices. Each invoice line indicates its price by indicating the id of the price so that it shows correctly later in reports.
What are the best practices for handling such situations?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我总是会根据每个订单项目存储购买时的实际名称、数量和价格，从而有效地对数据进行非规范化。订单是时间快照，不应与可能更改的事物相关。不仅价格会波动，而且如果您删除或重命名原始产品并且您的发票与之相关，会发生什么情况？
I would always store the actual name, quantity and price at the time of purchase against each order item, effectively de-normalising the data. An order is a snap-shot in time and shouldn't be related back to something that can change. Not only can prices fluctuate, but what happens if you delete or rename the original product and your invoice relates back to it?
将价格字段添加到发票行。
Add a price field to the invoice line.
我会回应你的建议：将价格分成一个单独的表，其中包含以下列：
PriceId、ProductId、Price、StartDate、EndDate。
这样您还可以提前计划未来的价格变化。可以使用（Sql Server 语法）获取当前数据：
I would echo your suggestion: Breaking out Prices into a separate table with these columns:
PriceId, ProductId, Price, StartDate, EndDate.
This way you can also plan ahead for future price changes. Current data could be gotten using (Sql Server syntax):