eCommerce – Use Case for Removing a Product

database-designe-commerceweb servicesweb-applications

I am starting to design the database schema for the eCommerce element of a web service I am creating.

The thing I'm trying to get my head around is how to deal with the use case of a seller (user of my web service) deleting a product.

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…

This problem is surely one that everyone developing such a system has come across. I'm just wondering if there is a best practice way to address it?

Can it be addressed at the database schema level? Or maybe it just requires certain validation checks on database data before allowing a product to be deleted, or to stop an error if a customer is trying to pay for an order that had a reference to a product, but that reference has been deleted (i.e. product was deleted).

Best Answer

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

Related Topic