Search code examples
mysqlshopping-cart

How to manage refunds db table in mysql for internet shopping mall?


I am currently working on designing a database table for my internet shopping mall. To specify the problem, I currently have:

  1. orders table : contains

    • order_id
    • total order price
    • order status (e.g. shipped, delivered, refunded)
    • orderer information such as address, zipcode and phone number
  2. products table : contains

    • product_id
    • price
    • stock quantity, and etc

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,

  1. order_details:
    • order_id
    • product_id
    • quantity where order_id and product_id are foreign keys and grouped as the primary key for the order_details table.

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?


Solution

  • You could create a refunds table and within put the following:

    • refund_id
    • order_detail_id
    • quantity

    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