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
    Commented Nov 2, 2021 at 18:46
  • 1
    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. Commented Nov 3, 2021 at 8:41

2 Answers 2

1

From a database and application design point of view, create a separate pricing table that holds the base price, tax rate, and any discounts. This table should link to the products table through a product ID. The structure could be:

pricing (product_id, base_price, tax_rate, discount, start_date, end_date)

This approach separates the concerns of product information and pricing. The products table will hold product-related data (like name, description, etc.), while the pricing table manages all pricing details. Also allows some flexibility by allowing you to carry prices for periods and tracking changes on product prices.

  • You can calculate the final price (including tax) on the fly for customer display and store the pre-tax price and discount in the orders table.

  • It allows you not to hit a product table when all you require is pricing information for accounting purposes only.

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 and acknowledge you have read our privacy policy.

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