I have several tables: product, category, addon in various relational relationships.

enter image description here

I structured it this way because: I have many products that belong to their own specific category. For each category there are specific addons.

Price Table

What I want to do is to add a pricing table that I will be able to query to find out things like

  • (main) How much does product addon cost for this specific product.
  • (optional) what is the current price for the product. That is, I can have several prices in the table that belong to the same product-addon, but have different timespans (dates) of being "active".

This is the structure I am thinking of using (updated as per comments/suggestions):

enter image description here

Is that a good way to structure the table? I would call this "Option 1", where "Option 2" would be to create a new table called product_addon as glue between product and addon and then use foreign key product_addon_id in my price table.

  • Would you please add ER diagrams? It's hard to understand option 2 without one. Commented Sep 1, 2016 at 21:05
  • Where do you define the M:M relationship? Isn't it already in some product_addon table? That's where the price should be.
    – Eric King
    Commented Sep 1, 2016 at 21:07
  • Also ER diagrams would add context that would make it easier to understand the problems and thus help you. Commented Sep 1, 2016 at 21:11
  • in process of adding ...
    – Dennis
    Commented Sep 1, 2016 at 21:16
  • How do you prevent addons that make no sense with a given product to be inserted in the table "Price"? For example ashtrays make no sense as a motorcycle addon. Commented Sep 1, 2016 at 21:31

2 Answers 2


Option 1 (because it is the most simple one), but I suggest you call the link table between products and addons not "price", better call it "product_addon".

Option 2 is only necessary if you need a list of prices for the same addon in a product (whatever that would mean).


I am not sure what an "addon" is. My intuition tells me that an addon is a type of product that cannot be purchased by itself. As such it should be represented in both the addon and product tables, and be identified by a product ID just like regular products. The addon table would include an additional foreign key or other data which details what prerequisites are required to purchase the addon.

As for where to store price... I would suggest your model is lacking a concept of "offer." An offer would include the product ID as a FK, and include start and end dates, plus of course the price. Because an addon is a type of product, no additional key to the addon table would be needed. You just need the product ID.

  • yes addon is a "product option" or "product feature" or "product base or subcomponent". It typically cannot be purchased by itself, and it has a pric.
    – Dennis
    Commented Sep 2, 2016 at 14:13

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.