I am working on a ecommerce project with laravel which has a ORDERS & PRODUCTS table.
`ORDERS table hasMany ORDER_ITEMS
`ORDER_ITEMS table has a reference to Product table.
Now when the admin deletes a product, We end up with false reference to the products table inside ORDER_ITEMS table. I thought about 3 ways to deal with this:
1: When the admin tries to delete a product which already exists in Orders , We prompt the user to delete the orders first.
2: Make use of Soft-Deletes in laravel for products table.
3: Create another table like ORDER_PRODUCTS or add particular columns in ORDER_ITEMS table.
I would like to know your suggestions regarding this particular problem and the best way to deal with this.
Thanks :)
In most cases that i have seen, ORDER_ITEMS should have no reference to original product row, it should be duplication of all data possible. Reason being, products are susceptible to change like name, price etc. They can refer to product row for future re-order purposes, but rarely be referenced by foreign key in order to fetch product details in order summary.
This way, you can either delete them or have a soft delete in place.
imo, I would add a column called "discontinued" of type date on the products table which default to null as it is more readable and understandable by all stockholders. It is like a custom soft delete function.
Please or to participate in this conversation.