I am currently working on designing a database table for my internet shopping mall. To specify the problem, I currently have:
orders table : contains
products table : contains
and since an order can contain multiple products, and a product can be contained in different orders, an intermediate order_details table to construct many-to-many relationship. Thus,
Now, I am trying to construct another table to store information about refunds, where customer can make partial refund request. (e.g. when customer X buys 3 items of product A and 2 items of product B, I want to let X refund only two out of three items of product A).
In the above example, one row will be inserted into orders table (say order_id = 1), and two rows will be inserted into order_details table for each product. Say product A has product_id = 1, and B has product_id = 2, then for (order_id, product_id, quantity), (1, 1, 3) and (1, 2, 2) rows will be inserted.
The question is, how should I construct refunds table so that the above specification can be satisfied? In the example above, when X wants to see his order status, how should I store information in the refunds table so that X can see that only 2 items of product A have been refunded, and other products have been ordered properly?
You could create a refunds table and within put the following:
This table will then store the refunded quantities for each order detail. To get the ultimate quantities ordered (excluding the refunds) you can easily do a left join to the refunds table from the order_details table and subtract the difference i.e.
SELECT (order_details.quantity - IFNULL(refunds.quantity, 0)) AS ordered_quantity
FROM order_details
LEFT JOIN refunds ON order_details.id = refunds.order_detail_id
This will always give you for each order detail, what has been correctly ordered. You can then sum these order details values to get what was correctly ordered for the whole order