0

I have designed an ecommerce database with products table to store product data

products (id, name, price, discount, discounted_price, tax_rate)

Here the price is including tax, discount is again including tax, discounted_price is including tax as well.

e.g, the price of product is 299, discount is 100 and the discounted_price is 199 with a tax rate of 5%.

In the orders table, the order is stored as

orders (id, sub_total, discount, tax_amount, total)

Here I am storing price before discount pre tax in sub_total, discount is storing pre tax, tax amount is stored in tax_amount and finally total amount with tax is stored in total

Now the issue here is that the price stored to customer is with tax. Also the discount is shown with tax but is saved as discount pre tax in orders database and if we generate invoice for the order, it gets data from orders table and shows a different discount amount.

How to fix this so that we have consistent pricing in products as well as orders table. Please note that I want to show price including tax to the customer, but the same cannot be stored in final orders table as we need to make account of taxes.

2
  • 3
    Easy -- don't store product.price, product.discount etc. with tax included; calculate amounts as and when needed.
    – mustaccio
    Nov 2 at 18:46
  • I fail to see what this has to do with database design. It is just an issue with having two different modes of encoding prices, with and without tax. There is nothing intrinsically wrong with that, as long as you know which numbers are with tax and which are not. You could begin by naming them accordingly so the difference is clear. Or you could make it so that all prices in all tables are always with tax. Or without tax. Or even have tables hold two copies, with and without tax. It's up to you. Nov 3 at 8:41
0

In the products table:

  • In the 3 columns (price, discount and discounted_price) do not include tax when saving data.
  • Add 3 new columns (price_including_tax, discount_including_tax and discounted_price_including_tax) and in these columns include tax when saving data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.