0

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.

1

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.

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.