How to delete products and persist order history when OrderDetails is coupled to Product.

Question

5.00/5 (1 vote)

See more:

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

Thanks,
robNo

Posted 7-Feb-13 6:30am

RobNO

Updated 7-Feb-13 6:32am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mike Meinz · Accepted Answer · 2013-02-07T07:26:00

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

Posted 7-Feb-13 7:26am

Mike Meinz

Updated 7-Feb-13 7:29am

v2

Comments

RobNO 7-Feb-13 16:36pm

Hi,

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.