I'm curious how to historically save a value obtained from a related table's field.
An invoice is a good example where the invoice item's price is derived from a related product table.
In future if the product price changes, you don't want the old invoice price to reflect the change.
Some posts I've read suggest creating a look-up table and having the Invoice table (Invoice::ProductPrice) look up the value from the look-up table .
Others suggest a script trigger to set a field in the invoices table (Invoice::ProductPrice) to the value of that in the Products table (Products::ProductPrice).
Is one method more common than the other and why (providing any of my mentioned methods are valid)?
If a look-up table is created, how do you get the product price into the look-up table?
Any insight as to accomplishing this would be greatly appreciated.