You need to keep history. Perhaps indefinitely.
You need a "logical" deletion flag usually a Bit data type column with a name like "ActiveProduct".
An active product that customers can buy would have the ActiveProduct column = 1. When a product is to be deleted from the current product catalog, the ActiveProduct column for that product is set to 0. All
Select
statements that execute on behalf of customers have as part of their
Where
clause, the additional clause:
AND ActiveProduct=1