[database design] table for even connections

Question

id | element
----------
 1 | Milk
 2 | Bread
 3 | Butter

Milk and Butter have "connection". And Bread and Butter have "connection". These are even connection, not parent-child.

How can i display them?

id1 | id2
----------
 1  |  3
 2  |  3

yes, but

id1 | id2
----------
 3  |  1
 3  |  2

also right, there are no first and second.

How can this be done?

Answer 1

To demonstrate, I'm going to invent the scenario that you're talking about recipes and ingredients. Perhaps you're not. Either way, you want to make a 'set' which contains any number of the 'items' in a many-to-many relationship.

Make a table called a "RECIPE" or similar, which looks like this:

• RECIPE_ID
• NAME

And another one called RECIPE_INGREDIENTS

• RECIPE_ID
• INGREDIENT_ID

Then for your example of bread and butter, in RECIPE:

ID | Name
-----------------
1  | Sandwich

And in ingredients

RECIPE_ID | INGREDIENT_ID
1         | 2
1         | 3

For something made up of Bread and Milk

ID | Name
-----------------
2  | Milky Bread

And

RECIPE_ID | INGREDIENT_ID
2         | 1
2         | 2