How i can assign to multiple categories multiple variants like colors, for example i have a product who can be in 3 categories but this product have 3 colors as variants how i can make a mysql structure to avoid insert in same row delimited by comma.
There is what is tried:
This is table for categories. |id|category_name| |1 | Woman | |2 | Men | |3 | Kids | This is table for product |id| name_product | |1 | New balance | |1 | Nike air | This is relationship when add a product to multiple categories |id|id_product|id_categories| |1 |1 | 2 | |2 |1 | 1 | This is relationship for variants | id | color_name | categories_id | product_id | | 1 | red | 1 | 1 | | 2 | blue | 1 | 1 | | 3 | green | 3 | 1 |
And what i want to do is to display all colors by categories but here i am stuck i dont know how to add multiple colors to a category when i add a product to multiple categories, sounds complicated…
I want to avoid by insert multiple colors id into a row delimited by comma being a bad practice.
What i want t accomplish is like this:
|id|id_color|id_categories|id_product| |1 | 2 | 1 | 1 | |2 | 1 | 1 | 1 | |3 | 3 | 1 | 1 | |4 | 3 | 2 | 1 | |5 | 2 | 2 | 1 | |6 | 1 | 2 | 2 | |7 | 1 | 3 | 2 |
I need to take each category and assign how many colors are inserted.
Advertisement
Answer
If I am understanding this correctly, you need to have multiple variants, such as multiple colors, for multiple categories. This is referred to as a many-to-many relationship. In order to handle this properly, you need to create more tables. If you want to go the simpler, but less dynamic, route, you can create a table called colors and add the colors into that table, then create another table, called an associative, or junction, table that lists the ids of the product and it’s associated color. You are actually on the right track with your example of:
|id|id_color|id_categories|id_product| |1 | 2 | 1 | 1 | |2 | 1 | 1 | 1 | |3 | 3 | 1 | 1 | |4 | 3 | 2 | 1 | |5 | 2 | 2 | 1 | |6 | 1 | 2 | 2 | |7 | 1 | 3 | 2 |
However, I would also stay away from creating a table specifically like this and go the extra mile to create all the tables properly the first time around, as it saves you much headache in the future.
The way I would handle this is to create a products table, and in the products table, reference a product category table with all the product categories. For example, a product “New balance” would have a reference to the “Shoes” category, through a foreign key placed in the products table.
For product variants, create another table called variants that will reference a variants category table in much the same way, in that you can have a variant, say “Red” with a reference to a “Color” category through a foreign key in the variants table. In this way, creating a new variant category is as easy as adding a new field to the variant category table.
Then, to associate the two, create a junction table that only references the id of the product and the variant(s) for that product. Through this table, you can pull all the variants associated with a product, or with some more advanced SQL, you can pull all the variants for a product that are associated with a certain variant category, such as “Color.”
I would suggest learning about Database Normalization and more specifically, getting your database to at least 3rd Normal Form. This will help you create better database structures that are more dynamic.