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

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 - 00:00:00 CET