I have designed an ecommerce database with
products table to store product data
products (id, name, price, discount, discounted_price, tax_rate)
price is including tax,
discount is again including tax,
discounted_price is including tax as well.
price of product is
100 and the
199 with a
tax rate of
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
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.