Click here to Skip to main content
15,791,739 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Hi All,

I am currently facing a little bit of a delima. You see I have this product/shopping cart/order system. In this system I have the OrderDetails table coupled to the Product table with a relationship of One Product can have many OrderDetails.

If a user wanted to delete one of their products, how could I persist the OrderDetails so that any user that purchased a product from the user that deleted the product will still maintain all of the OrderDetails. A thought is to decouple Products from OrderDetails and add redundancy to the OrderDetails table.

What are your thoughts? Let me know if you need more clarification!!!

Updated 7-Feb-13 7:32am

1 solution

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
Share this answer
RobNO 7-Feb-13 16:36pm    

I like this idea, but would you recommend doing the same thing for deleting a user, if a user can have zero or many products?

Is it to any dis advantage to just capture the products current state in the order details table, other then if its redundant? Or is redundant data extremely evil?

Sorry for all the questions. Just looking to learn best practices and I am having trouble finding resources.
Mike Meinz 7-Feb-13 16:46pm    
Redundant data makes it more difficult to change attributes. Should the product description change, it would be in many records rather than just one place. Ideally, you shouldn't duplicate data.

Even if your customer tells you they don't want historical information on users, they will, most likely, eventually come to you and ask for it. By using a similar technique, you will be able to satisfy their change in requirements. It is also useful if they ever ask you to produce historical statistical reports. A good software developer plans for changes in user requirements.

Also, use an IDENTITY column as the primary key of your database tables. Columns like product code, customer number, item code, promotion code, etc. should not be used to link tables together (foreign key). The IDENTITY column value should be used to do that. This lets the user easily change one of those codes. For example, if thy said: "Oops, we put in the wrong product code for that item. We need to change the product code." That could be done easily since the product code was not used as a foreign key.
RobNO 7-Feb-13 16:54pm    
Wow, thanks for the well written answer and the IDENTITY recommendation!

much appreciated!
Mike Meinz 7-Feb-13 17:06pm    
Please accept the solution so that the question gets marked as answered.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900