I have a question please.
I am developing an online mall website.
I have an orders table.. I have put the product_id as an attribute in order to know which product is ordered.
In the customer’s purchase history page, I need to present all the orders purchased by the customer, including the product title and product image which are not included in the orders table.. In order to get them we can select and join order table and products table.
But what if the store owner deleted the product from the products table after a certain time, and I want to still present the product’s title or image in the purchase history page.. What can I do?
If the solution is to repeat them as attributes in the orders table.. Isn’t it considered as redundancy?
Advertisement
Answer
The solution here is virtual deletes.
In the products table, add a IsDeleted or perhaps Active column with a default value. If the store owner “deletes” a product, flip the flag to it’s opposing value. This retains the data on the product without stopping store owners from trimming products, and also allows for the easy reinstatement of a product.
Queries will need to be tweaked to account for the flag, but that’s a small price to pay for data integrity.