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

Hi:

I have a db design question about how to handle returned goods.

I am designing a database for a shop. This is the first time I am working on such system.
I have created the following tables with columns:

UserTable:
userid (PK)
firstname
lastname
...

AccountTable:
accountid (PK)
accountcreationdate
...

UserAccountTable: (join table between UserTable and AccountTable) useraccountid(PK)
userid (FK)
accountid (FK)
mainaccount_holder_flag

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

OrderItemTable
orderitemid (PK)
orderid (FK)
quantity_ordered
quantity_shipped
itemtotalPrice
...

and a bounch of other tables to handle "shipping" and "payment".

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?

If you reply, please send a copy to zlmei_at_hotmail.com because I am not read the newgroup that often.

Thanks in advance.

Guang

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jan 07 2000 - 09:05:15 CST