3

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.

Community
  • 1
  • 1
Lishna
  • 29
  • 2
  • Soft delete would be the better way. In other way, you can have a separate table to hold historical data and hard delete it from original table. – Viki888 Nov 10 '16 at 11:41
  • @viki888 thanks for it but currently we are planning to use soft delete with IsDeleted and DeletedDate fields.In future if needed we would move to hard delete and archive the data in different table. – Lishna Nov 10 '16 at 13:56

1 Answers1

1

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.

Andy
  • 1,307
  • 1
  • 12
  • 17