MySQL - How to store multiple combinations of colors for one product?

Question

I am searching for the best solution for the following issue:

I have three tables:

1. Product

id	productName
1	Tshirt
2	Pullover
3	Jacket

2. Base Color

id	baseColorName	allowedExtraColorIds
1	Green	1,2
2	Blue	2,3
3	Red	3

3. Extra Color

id	extraColorName
1	Purple
2	Orange
3	Black

So as you can see, there is a dependency between the tables Base and Extra Color.

• So not each Extra Color can be used with each Base Color. For the Base Color "Green" only the Extra Colors with the Ids "1 and 2" are allowed to pick.

Now the Table Product contains the products. However I now want to have a form where the user can first select the product and then he can map combinations of Base and Extra Colors to this product.

Example:

1. The user selects the "Tshirt" as a product
2. Now he creates Combinations:

• He selects the Base Color "Green" and he selects the allowed Extra Color "Purple"
• He selects the Base Color "Red" and he selects the allowed Extra Color "Black"

And now I am wondering how to save this information, so the mapping of the different combinations of Base and Extra Colors for the single product in a MySql Database.

Has anyone an idea how to achieve this? :)

Answer 1

You have to create tables like below to store generated combinations.

1. selected_products

   id	product_id	user_id
   1	1	1
   2	2	1

2. selected_product_combinations

   id	selected_product_id	base_color_id	extra_color_id
   1	1	1	1
   2	1	3	3

Here, selected_product_id is a foreign-key reference to the primary key of selected_products table.

Answer 2

Elaborating a bit on what @RiggsFolly already suggested:

• work with 1 table of colors. Having two of them will only complexify the situation
• create a table where all possible combinations are listed as individual items - you could even consider adding the product to allow scenarios where not all colors are available for all products.

Like this

Product	Basecolor	Extracolor
1	1	1
1	1	2

That way, all checks become a simple singleton query that either gives a result (combination allowed) or doesn't (combination not allowed)

This will change your approach a bit but as said, that's how you do things with RDBMS's. It will save you a lot of headaches afterwards.

Yes, to propose all possible combinations you will retrieve a result set instead of one record, but that is easily solved.