Re: Database design Question: How to handle returned goods (items)?

In article <854v43$pq9$1_at_nnrp1.deja.com>, gmei_at_my-deja.com writes

>
>OrderTable:
>orderid (PK)
>useraccount(FK)
>orderdate
>ordertotalPrice

This table isn't normalised if you keep this total price field. The order value is the sum of the values of the items ordered. If the value of the order changes, such as when something is returned, you have to recalculate this field. That's not a good idea.

[...]

>But assuming a customer bought some items and later decided to return
>certain items.
>How should this be handled in the database in general?
>
>Right now I can think of two possible approaches:
>
>1. Add a column "quantity_returned" in OrderItemTable.
> When a customer return certain item, we get its "orderid"
>and "orderitemid", and
>update the orderitem record. we will have a trigger in that table to
>update "itemtotalPrice"
>and "ordertotalPrice" (in OrderTable).
>
>The problem is there are changes in two tables and the original data
>gets modified, we need to
>create "order_history_table" and "orderitem_history_table" to store the
>orginal data.
>
>2. Create a totally separate table to handle returned goods:
>
>ReturnedItemTable:
>returnitemid(PK)
>accountid(FK)
>original_orderitemid(FK)
>quantity_returned
>return_item_total_credit
>
>This way it seems easier to handle in the database, A lot less change.
>
>I am leaning toward the 2nd approach. What is your comments and
>suggestions?
>Any other better approaches?

Yes. First throw away the order total field unless you are willing to accept the problems associated with denormalisation.

Second, when a customer returns goods either modify the quantity ordered or even better, add a new detail line with a negative quantity. You can then get the order value by summing the values of each detail line.

This also copes with the situation where the store charges a restocking fee. Enter the number of items returned (as a negative) and enter a reduced unit price.

-- 
Bernard Peek
bap_at_shrdlu.com
bap_at_shrdlu.co.uk