How can I keep a sales history in my database?

It's crucial to maintain the static data for sales, possibly by capturing sales info in a way that doesn't link to updated product records. This way, your sales data remains preserved, allowing for compliance and accuracy in historical reporting.

You might consider designing a new entity just for recording sales. This entity would link to the products for relevant information but also have a direct column for the price paid. This way, no matter how many times the product changes, you’ll always have the original sale price saved.

For your scenario, I'd suggest adding a price column directly in your LineItem table when a sale is created. This way, the sale retains the exact price at the time of purchase irrespective of future product price changes.

One option is to create a separate pricing table to track historical prices. You can use a trigger or a query to record the price and other details when the product gets updated. This way, your main product table stays clean, and you can keep track of changes over time.

Instead of updating products, create a historical product version each time there's a change, setting a flag on the old version to mark it as deprecated. This keeps your history intact and avoids any confusion with past sales.