I am creating a shopping cart and an currently planning / building the checkout process. Everywhere I look I see the recommendation to have an order table, and in that order table have a productId for the product that is being ordered... That seems nice. But I can not seem to get over the issue of what happens of that product is updated or worse deleted? Then the record of what is ordered is updated or deleted. Would it be better to duplicate the product details into an OrderedProduct table so you have a snapshot of what the product looked like when it was ordered? Then even if the product is changed or deleted, you have all the information from when it was ordered? That is what I am doing, but want to make sure I am not making a mistake or there is a better way to accomplish it.
If the item changes or gets updated, it should get a new ItemID. You need the historic record. So say you have a shirt and it comes in blue ItemID Shirt. If you offer it in Green change the new ItemId to be ShirtGreen. iF it is an upgrade, say offering blue shirt with a pointed collar: ItemID: ShirtPointedCollar.