How to structure and connect my "price" table to existing relational database table structure?

Question

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.

• Tulains Córdova: Would you please add ER diagrams? It's hard to understand option 2 without one.
• Eric King: Where do you define the M:M relationship? Isn't it already in some product_addon table? That's where the price should be.
• Tulains Córdova: Also ER diagrams would add context that would make it easier to understand the problems and thus help you.
• Dennis: in process of adding ...
• Tulains Córdova: 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.

Answer 1

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).

Answer 2

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.

• Dennis: 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.