Today I learned about Many-to-Many relationships in relational data models
- A many-to-many relationship is a type of cardinality that refers to the relationship between two entities A and B in which A may contain a parent instance for which there are many children in B and vice versa.
- For example, a recipe may have many ingredients and a specific ingredient may be used in many recipes.
- In SQL, this relationship is handled by an associative table. The primary key for this type of table is composed of two different columns, both of which reference columns in other tables that you are associating together.
- It is a good convention to name these associative tables by how they reference each other: Table1_Table2
- A SELECT statement on an associative table usually involves JOINing the main table with the associative table.
- If you don’t want to run these JOINs every time, create a view first. A view is a virtual table based on the result-set of an SQL statement. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
Example SQL for creating an associative table:
CREATE TABLE recipes_ingredients (
RecipeID INT(11) REFERENCES Recipes (RecipeID),
IngredientID INT(11) REFERENCES Ingredients (IngredientID),
PRIMARY KEY (RecipeID, IngredientID)
)