CODEWITHSUNDEEP
mysqldatabase
Subhan
Subhan

Reputation: 1634

Creating self referencing ine to many relation in mysql

I'm creating a table named finishProducts that has the following columns:

Each finish product may be composed of more than one components, these components should also be one of the finish products. So it is like one to many relationship where one finish product may or may not have more than one components (Finish Products).

What is the best practice to create a table for such scenario?

One of the solution that i think is to add multiple rows for same finish product with different product components. See attached Image.

Solution 1

But this solution might not be the best one because in future i might have to add another column named Color for this table where i would have to apply the same one to many rule (one product can have multiple colors).

So i'm searching for a best possible solution.

Upvotes: 0

Views: 300

Answers (1)

maxtwoknight
maxtwoknight

Reputation: 5346

Why not have two tables, one for products and one for components. Then have a one to many relation ship to the components.

product_id | product_name 
--
12 | Epoxytile


Component_id | product_id | Component_name
--
1 | 12 | Expoxytile base
2 | 12 | Epoxytile grout
3 | 12 | Expsytile Silica

You can have however many components you need then. To query for them, just use 

SELECT product_id, p.prouduct_name, c.compoent_name
FROM product p
     INNER JOIN compoent c ON p.product_id = c.product_id

Upvotes: 1

Related Questions