CODEWITHSUNDEEP
phpmysqldatabaselaraveleloquent
Chris
Chris

Reputation: 61

What is the right way to store 'historical data' into database in laravel?

I'm creating an E-commerce mobile application by using Laravel as back-end development.

Scenario: I have a Product table which store some information about the product. Whenever user buys a product, he will get a purchase history record (which will show the product information) that will be stored into the database. So when the seller update their product information, the product information of the user's purchase history record will not be affected.

Problem: If I just simply create a user's purchase history table to store the record for each user, I think it will be a lot of spaces required in the database.

Question: Is there any better way recommended to store the purchase history record? Or is there a way to create something like a 'snapshot' for the record before the product has been updated or deleted by the seller?

Upvotes: 4

Views: 3892

Answers (1)

Kenny Horna
Kenny Horna

Reputation: 14241

Well, this is more of an opinion-based kind of question.

The method that I use personally is the one that you also have in mind: to store the relevant data of product in a separate table. Let's say you have your users, products, and orders tables. A user has many orders, and an order has many products. I use an aditional table details to store product-related info (basically, the relevant data) so, in case of changes in the origintal product, the info still remains correct. You could also store this info in a json column on your purchase history.

Another approach could be to create new product objects whenever these are updated:

  • Product A (id: 1): price - $10 - 01/08/2019
  • Product B (id: 2): price - $25 - 01/08/2019
  • Product A (id: 3): price - $11 - 05/08/2019 (after an "update" on the price of the product)

This way, if someone buys a Product A on 02/08/2019, the price will be $10 and the foreign key in your history will point at the product_id = 1. Of course that this approach isn't the one that I'd use because reporting will be more difficult to handle.

Upvotes: 3

Related Questions