Click here to Skip to main content
12,827,807 members (47,694 online)
Rate this:
Please Sign up or sign in to 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!!!

Posted 7-Feb-13 7:30am
Updated 7-Feb-13 7:32am

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 3

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

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170326.1 | Last Updated 7 Feb 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100