eCommerce use case: removing a product

Question

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).

Answer 1

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

Answer 2

We handle it via a status field. Products can be active, inactive, backordered, advance_sale, superseded, or discontinued. Only active, advance_sale and backordered products are displayed to the user.

What to do if the state changes during a transaction is a business requirement. Some sellers might want the customer notified and the item removed from the cart. Some might allow customers with the item in their cart to check out, but the item can't appear on new orders. We have a per-store product eligibility verifier that checks the products in a customer's cart whenever a cart activity occurs (add/remove item, change quantity, start checkout). If you support wish lists or recurring orders, be sure your solution takes those into account as well.

Answer 3

Don't delete; mark it discontinued or unavailable, like Mike pointed out in the comments. If the product is discontinued, you simply don't show it on sales pages any more, but your database references are left intact. If there is a pending order for that product, then the business side (not the database) has to figure out how to handle it: do we still ship (assuming any is in stock) or just issue a refund?

That said, if the item is not used in any shopping carts, orders, etc. -- for example, it's just being used for training someone or was somehow created inadvertently -- then, sure, delete away. But you need to do lots of integrity checking even in this scenario to be sure a deletion is safe; you're still probably better off marking it discontinued.

Answer 4

We would not allow our system to physically delete the product. We simply turned the Active flag to "No". This allowed the archived invoices to produce correctly.

Updated 12-26-2013: There is a bigger issue at stake here

After re-reading the OP's question it seems that this web service will be used by more than one seller. If that is the case then there is a many to many relationship between Seller and Product. So not only should the Product table contain an Active boolean flag the Product table needs a ForeingKey that identifies the SellerId.