History price list for invoices?

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    |                |
| o开发者_如何学JAVAption_id      | int(11) | NO   |     | NULL    |                |
+----------------+---------+------+-----+---------+----------------+

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.

The best thing you can do is creating a a column in order_items for 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.