I have recipes in mysql database (for website) that I will need to attach to other recipes as a parent child relationship. I need one recipe to be able to have multiple parent recipes. Initially I thought I could have a parent_id column in the recipes table, but if a recipe has multiple parents that doesn't work. I then thought I would just need to make a look up table where parent_id and child_id match up, but both the parent and child are recipes, so how would the query know to differentiate which is parent and which is the child when I do the select statement? Does this make sense??
I would use a tow tables: recipes(ID, ...) and connections(pID, cID)
Every recipe have a ID and inside connections you save a parent-child connections.
recipes will look like this
(0, Soup)
(1, Cookie)
(2, Cookiesoup)
connections will look like this
(0, 2)
(1, 2)
To query all child recipes of Soup with ID = 0 you can use this
SELECT * FROM recipes JOIN connections ON recipes.ID = connections.cID WHERE connections.pID = 0
To query all parents recipes of Cookiesoup with ID = 2 you can use this
SELECT * FROM recipes JOIN connections ON recipes.ID = connections.pID WHERE connections.cID = 2