I'm trying to develop a new products database for a client and I am having a hard time finding a solution to handle the many different prices a product can have.
Variables that can affect the price of a product:
- region (where the customer lives).
- customer type (whether they're a new customer or a registered customer).
- order type (whether you want to purchase the item once or subscribe monthly).
- store type (there were many store types, but now they're transitioning to one, but I don't know if they will add more in the future again so I want this to be flexible
Product Table
| id | product_name | sku | description |
|---|---|---|---|
| 1 | Vanilla Protein Powder | 1111 | This is a description... |
Attributes Table
| id | associate_type | region | store | currency_code | order_type |
|---|---|---|---|---|---|
| 1 | 2 | 1 | 11 | usd | 1 |
| 2 | 2 | 1 | 11 | usd | 2 |
Product Attributes Table
| id | price | product_id | attribute_id |
|---|---|---|---|
| 1 | 49.95 | 1 | 1 |
| 2 | 29.95 | 1 | 2 |
I was initially thinking this would be a good route to go, but the issue that arises is on the product page I want to display the price of the product if you want to purchase it once and the price if you wanted to subscribe. The same issue would happen if I also wanted to show a list of product cards on a product category page displaying both prices.
The issue I run into
When I write a query to get the correct products with the correct price lines, I would filter all the products by the variables mentioned above, but I will always want the subscription price and the one-time price, so in the query, I would have to include WHERE order_type = 1 AND order_type = 2 but doing this would return duplicate records of the product/ products because of the different prices for a one-time purchase and a subscription purchase.
Is there a better way to set up the tables I have so that the query would not return duplicate records of the product/ products? Or is there a way to write the query to handle this for me? Or do I even need to switch up my database design altogether?