Tables Versioning (historical Tables) And Order Tables Relationship

If I add a new product in the products table or change the data - it will automatically add into products_history. This is done by trigger. When customer placed the order, there is

Solution 1:

One danger with only recording a single timestamp is that it's hard to determine what the latest entry is for a given order. You have to use a subselect or a function. I'd recommending having start_timestamp and finish_timestamp, with the latter being NULL for the latest entry. When an entry becomes history, it gets its finish_timestamp filled in. There are other ways to deal with this problem, I'm sure, but this seems to me to be the most straightforward.

Solution 2:

I really couldn't understand what you are trying to achieve with history tables. If the products price changes rapidly and you want to save the products base price at that current time why not put it into order_product ?

orders :order_id/created_at/status(PK:order_id)order_products :order_id/product_id/quantity/product_price(PK:order_id,product_id)

this way you wouldn't have to join product_history every single time. you can do same denormalization for extras aswell.

P.S : try not to use reserved words like "order" in table / column names, it can introduce bugs.