Best approach to our database design

Question

We have a database for an e-commerce application, we are working with JPA 2.0.

We have these three tables: products(id, code, name, description, price); soldproducts(id, quantity, product_id); orders(id, date, status, comment).

Here is our problem: when we delete a product from products table we have a constraint problem in the soldproducts, because the product_id column references a product. We want to delete the product, but we want to have access to the products details.

We want to add an aditional column to the products table, something like availability, which tells us if the product is available or not. With this approach, if we delete a product from the application and the product is not referred from the soldproducts, we are just updating this extra column to unavailable, we don't delete it. Otherwise, we delete the product from the table. But we are not sure if this is the best approach.

What do you think, what is the best design approach for this given situation?

Answer 1

The best design approach, in my opinion is to not delete the product row but to mark it as deleted. You will need to add a deleted or active boolean column to your table.

This is what's usually done in this situation on most systems I saw/worked on. Of course, you will need to filter your products query by active (not deleted) products every time. But it's the only way to not loose the historic data references.