Historical data

[jnewman67]

as part of the last discussion i was in, it reminded me that there is
a real, thought not critical, issue with historical data where past
orders are concerned.

the order_item table should be updated to contain all the relative
information for an order, as well as the orders table itself. things
should not be recalculated/looked up when an order is pulled up, as
those calculations/lookups are based on current defaults and settings,
and not from the values at the time, and those may have changed since
the order was placed.

one simple example is the description of an item. that is not stored,
but pulled form the current product list when displayed. if that
description changed since the order was placed, or if the item was
deleted, then the old order won't display the correct information.

i'm sure there are others, and i haven't put much investigation into
it, but the thought is still nagging at me.

[jnewman67]

okay, a followup. had to actually implement this yesterday, so i
thought i'd pass along what i found and did.

first, most everything is already in there, so i only had to add
product_sku and product_name to the order_items table (i added them
after the product_id field).

then, i updated the ps_checkout add() and paypal_add() functions (if
you have paypal integrated) so that those values were stored in the
table under their respective new fields.

finally, the display mechanisms need to be updated. orders/
order_print and checkout/thankyou need to have the DBCART query
updated. remove the table PRODUCTS from the FROM line of the query
and remove it's product.product_id = order_item.product_id reference
as well. then instead of

$ps_product->get_price($dbcart->f("product_id"))

use

$dbcart->f('product_item_price")

product_sku and product_name will take care of themselves, as they are
in both products and order_items.

same thing will have to be done in order_items:

it's DBCART instead of DBOI, and change references like

$ps_product->get_field($dbt->f("product_parent_id"), "product_name");

to

$dbt->f('product_name")

(same thing for price and sku).

i think that shoudl do it. by doing all this, you prevent any changes
to product entries from carrying over into historical data. what they
bought remains intact, even if products get changed or reused.