The standard approach is to mark the product as inactive, discontinued or otherwise not visible. Deleting an entry from the database has an entire cascade of events that can be problematic.
This could be something as simple as a boolean, or a MySQL-esque enum, or its own table to join in (if you want to distinguish the difference between inactive (not yet available and not visible) and discontinued (previously available and not visible).
Consider a schema that is something like:
+---------------+ +----------------+
| Product | | Product_state |
|---------------| |----------------|
| id (PK) | +--+ id (PK) |
| status (FK) +--+ | visibile |
| sku | | desc |
| vendor (FK) | +----------------+
| desc |
| ... |
+---------------+
Make sure you have all the appropriate indexes on the tables (see Bitmap vs B-tree in Oracle and bitmap index at wikipedia). This, combined with the proper view will allow you to simplify the query logic.
Rarely do products exist on their own. As you mention:
The issue I have is that this product could be referenced in a customers basket, to be paid orders, pending orders, deliveries, past orders etc...
Removing the product from the database would either leave dangling references to things that don't exist. Even more 'fun' is if the database was constructed with foreign keys that cascade, that cascade of events becomes even more "fun" with a cascade of deletes that wipes out data, or sets the values to null
(depending on how the cascade is set up - see Foreign key at Wikipedia). Note that you can use this approach in some RDBMs to prevent people from messing up the master table by using the restrict
option on foreign keys.
You wouldn't just use this for 'in a cart' as a thing, but as you mentioned, paid orders, pending, deliveries, warranties, etc... Restricting the foreign key delete will give you a database error if you try to delete where it shouldn't be and it will be enforced by the database itself, rather than trying to implement checks in code.
Using the foreign key constraints will also make sure that your database maintains referential integrity - preventing people from accidentally linking an order to something that doesn't exist (which is just as bad as deleting). It can also improve performance of reads (see Does Foreign Key improve query performance?)
For oracle, you can read about the constraints on foreign keys: 13.1.17.2 Using Foreign Key Constraints
For mysql, you can read about 14.2.7.6 InnoDB and foreign key constraints