sql - soft delete or hard delete good for eCommerce -

i have read this post concerned best solution ecommerce site

our scenario:

product table

productid name price 

orderdetails table

orderid productid  

orderdetails table has fk productid referrenced productid of product table

once product has been deleted, how going display historical order report?

options:

soft delete disadvantage - affects db storage performance

hard delete disadvantage - need join query while taking report

any great.

i go soft delete. if in e-commerce context.

how storing deleted products in archivedproduct table , doing following:

select * orderdetails right join product on orderdetails.productid = product.productid union select * orderdetails right join archivedproduct on orderdetails.productid = archivedproduct.productid 

when say

it affects db storage performance

yes, there overhead in terms of performance entirely dependent upon size of 3 tables.

if @ later stage wanted increase performance of query, either wipe out of "deleted" products archivedproduct table based on own considerations (for example, products inserted prior ...) or add constraints second select statement. you'd still in safer position hard delete.