发票的历史价目表？
当客户下订单时，item_id 和 option_id 存储在 order_items 表中，从那里它将为客户生成发票。然而，该商品的价格每隔几个月就会发生变化，这会影响旧的发票信息。
有哪些解决方案可以解决这个问题？我不想将价格和商品名称存储在 order_items 表中。
我读过可能的解决方案是创建 history_prices 表（通过触发器审核系统或通过 php 手动 SQL 插入查询？），审核是最佳解决方案还是还有其他解决方案？
您能否提供示例如何创建history_prices表，以便当我从item_options.option_price更改价格时 - 它将存储到history_prices表中？
现在 item_options 表中有超过 200,000 行，我需要将价格复制到history_prices 中吗？
我需要一种有效的方法，以便发票不会受到新价格变化的影响。
item_options 表：
mysql> desc item_options;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| option_id | int(11) | NO | PRI | NULL | auto_increment |
| item_id | int(11) | YES | MUL | NULL | |
| option_name | varchar(100) | YES | | NULL | |
| option_price | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
order_items 表：
mysql> desc order_items;
+----------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+----------------+
| order_items_id | int(11) | NO | PRI | NULL | auto_increment |
| order_id | int(11) | NO | | NULL | |
| item_id | int(11) | NO | | NULL | |
| option_id | int(11) | NO | | NULL | |
+----------------+---------+------+-----+---------+----------------+
When the customer place an order, the item_id and option_id are stored in the order_items table, from there it will generate invoice for the customer. However the price of the item always change every few months and it will affect old invoices information.
What are the solution to fix this problem? I do not want to store the price and item name in the order_items table.
I have read the possible solution is to create history_prices table (audit system via trigger or SQL insert query manually via php?), is Audit best solution or is there any other solution?
Can you provide example how do I create history_prices table, so when I change the price from item_options.option_price - it will be stored into history_prices table?
Right now I have over 200,000 rows in item_options table, do I need to copy the prices into history_prices?
I need an efficient way so the invoices will not be affected from the new price change.
item_options table:
mysql> desc item_options;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| option_id | int(11) | NO | PRI | NULL | auto_increment |
| item_id | int(11) | YES | MUL | NULL | |
| option_name | varchar(100) | YES | | NULL | |
| option_price | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
order_items table:
mysql> desc order_items;
+----------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+----------------+
| order_items_id | int(11) | NO | PRI | NULL | auto_increment |
| order_id | int(11) | NO | | NULL | |
| item_id | int(11) | NO | | NULL | |
| option_id | int(11) | NO | | NULL | |
+----------------+---------+------+-----+---------+----------------+
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您能做的最好的事情就是在
order_items中创建一个价格列。这也是最直接的。如果您想创建一个包含价格历史记录的表以供报告使用，那可能没问题。但不要仅仅为了获取某些商品的价格而查询价格历史记录而让自己感到痛苦。价格是商品的一个属性。价格可能会因促销、折扣、特别优惠等而发生变化。
The best thing you can do is creating a a column in
order_itemsfor the price. And that's also the most straightforward.If you want to create a table with price history for reporting use, that might be fine. But do not give yourself the painful headache of querying the price history just to get some items' price. The price IS an attribute of the item. The price might change due to promotion, discount, special offer, etc.
检查以下设计：
设计 1：存储项目更改的滚动历史记录（如果是新行任何更改：名称、描述、价格）。
设计 2：仅关于价格变化的新行。
或者，您可以将价格与订单本身一起存储。
Check the following designs out:
Design 1: Stores a rolling history of changes to the item (New row if anything changes: name, description, price).
Design 2: New row on Price change only.
Alternatively, you can store the price with the order itself.
这是一个见仁见智的问题，有些人会同意历史价格查找就可以了，我的意见是不行。
查找价格历史记录并从中确定发票价格的问题是存在很大的出错空间。您将使用多个逻辑来确定正确的价格，所有这些都容易出错。您可能忘记转换发票的时区，这可能会导致其位于价格更改的错误一侧。您可能会忘记将任何应用的折扣或优惠券代码设置为日期敏感等。不断变化的运费怎么样？
最好将实际发票价格与发票本身一起存储。磁盘空间很便宜，利用冗余让晚上睡得更好。
This is a matter of opinion, some will agree that a historical price lookup will be ok, my opinion is that it is not.
The problem with looking up a history of prices and determining the invoice price from that is there is plenty of room for error. You will have several pieces of logic used to determine the right price, all of which are prone to errors. You could forget to convert the time zone of the invoice, and this could cause it to be on the wrong side of a price change. You could forget to make any applied discounts or coupon codes date sensitive, etc. What about ever changing shipping charges?
It is best to store the actual invoice price with the invoice itself. Disk space is cheap, use the redundancy to sleep better at night.