I'm facing a scenario in my e-commerce setup where after an order is marked as Done, any subsequent changes made by the customer to their information (like shipping address) are reflected incorrectly in past orders. I stumbled upon a blog post that discusses three solutions in PostgreSQL:

  1. denormalizing the Order table
  2. using Temporal Tables
  3. storing snapshots as JSON blobs.

I am keen to understand which of these methods, or any other recommendations you have, effectively tackle this issue while being scalable for a small business setup. I value insights from individuals who have witnessed or implemented these solutions in various company sizes and am interested in learning about what worked or didn't work in your experience.

Source Link

  • Primarily opinion based, so off-topic. All these options are reasonable, it depends on what yiou want to do with the data afterwards, and how complex the data is. Oct 26, 2023 at 16:10
  • All solutions can be ok, depending on the situation, the frequency of changes, the number of columns affected etc. Whether you want to use JSON or not is just an implementation detail. Oct 27, 2023 at 4:40
  • Read up on slowly changing dimensions
    – mustaccio
    Nov 1, 2023 at 21:01


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.