CODEWITHSUNDEEP
sqlsql-serversql-deletearchive
Lishna
Lishna

Reputation: 29

soft delete or hard delete good for eCommerce

I have already read this post but I am concerned the best solution for eCommerce site

Our scenario:

Product table

ProductID Name Price

OrderDetails table

OrderID ProductID

OrderDetails table has FK ProductID referrenced to ProductID of Product table

once product has been deleted, how are you going to display the historical order report?

Options:

soft delete disadvantage - it affects db storage performance

hard delete disadvantage - need extra join query while taking report

Any help would be great.

Upvotes: 3

Views: 908

Answers (1)

Andy
Andy

Reputation: 1386

I would definitely go with soft delete. Especially if in an e-commerce context.

How about storing deleted products in an ArchivedProduct table and then doing the following:

SELECT
*
FROM
OrderDetails RIGHT JOIN Product ON OrderDetails.ProductID = Product.ProductID
UNION ALL
SELECT
*
FROM
OrderDetails RIGHT JOIN ArchivedProduct ON OrderDetails.ProductID = ArchivedProduct.ProductID

When you say

it affects db storage performance

Yes, there is an overhead in terms of performance which is entirely dependent upon the size of the 3 tables.

If at a later stage you wanted to increase the performance of the query, you could either wipe out some of the previously "deleted" products from the ArchivedProduct table based on your own considerations (for example, all products inserted prior to ...) or add some constraints to the second SELECT statement. You'd still be in a safer position than with a hard delete.

Upvotes: 1

Related Questions