Products with different prices in an order

database-designschema

I have a "classic" database schema for order and products:

Orders
------
id
etc.

Products
--------
id
name
etc.

Order Products  --> Pivot table
--------------
id --> some people don't like this, nevermind.
order_id (FK)
product_id (FK)
etc.

Now I have to deal with prices inside orders. Two problems:

  1. There are two currencies -dollar and euros- and price are not calculated. Let's say, sometimes a product costs $79 vs 89€, some other times $89 vs 99€
  2. Some products can be bought in a bundle and their prices can vary. Buying only product "A" costs $4, and buying product "A" with product "B" costs $3 + $3 (product "B" costs $3 in the "A"+"B" bundle)

Is there any simple, elegant and readable way to deal with such a problem? Maybe some part of the solution could be done outside of database, not sure.

Best Answer

Below is my take on it:

  1. Have two separate price columns, once for $ and the other for €. If in some stage in the future you will start taking payments in more currencies, you will likely use one of the prices as the base price and do a currency conversion calculation.
  2. Depending on the complexity of the rules you have two choices:
    • a. Add "bundles" and "bundled products" tables which will store information about discount prices and what product combinations are eligible.
      Pros: You can easily implement functionality to manage bundles in the front end.
      Cons: Complex ad-hoc rules cannot be implemented this way.
    • b. Implement order price calculation rules in code (either database side or front end side, which will calculate final order price based on the products added.
      Pros: This approach allows for any number of complex rules specified.
      Cons: Requires program changes to change the rules. The rules are not transparent to administrators / customers.
Related Question