Sometimes a one to many relationship isn't enough.
In some cases you need to be able to have multiple records on both sides of a relationship.
For example an article can be related to in many categories and a category can be related to many articles
to model a Many to Many relationship between tables.
We need to add another table.
This is sometimes referred to as a join table.
There are many to many relationship is basically two one to many relationships via the join table.
Let's add a table to our database to model a Many to Many relationship between the article and the category
tables.
We'll call it Article category and we'll give it to columns.
The first column is a foreign key to the article table called Article idae which is an integer.
The second column is a foreign key to the category table called category idae which is also an integer.
Our quick save.
And there's the table.
In order to uniquely identify each row of a table we need a primary key.
In other tables we've added an integer i.e. D column as this is a join table.
This isn't necessary though.
We can create an index on two columns.
Make it primary and select both columns in the table.
This primary key will uniquely identify every row of this table every row being the combination of an
article and a category.
Next we can add the foreign key constraints to this table.
First the article idae is a foreign key to the article table and the idae column.
Then the category idae.
Is a foreign key to the category table and the I.D. column there.
Will Change the constraints to cascade because if an article is deleted we want any corresponding records
in the join table to be deleted too.
And likewise for categories.
Our quick save.
And that completes the join table.
Let's insert some data as we've defined foreign key relationships.
We can select the articles and the categories in lists.
I'll go ahead and add various regards.
Now that we have a many to many relationship between the article and category tables we can remove the
category idae foreign key from the article table before we can remove this column.
First we need to drop the foreign key constraint which we do in relation view by clicking on drop.
Then we can do the same with the category idae field.
To select data joining the tables in a Many to Many relationship.
First we join one table to the join table.
Just like in a one to many relationship.
Then we add a second j'aime joining the second table to the join table the join key word refers to the
most recently mentioned table.
So with this esky you l will select all the articles and their corresponding categories joining them
via the article category joined table.
As with the one to many join.
This is a full j'aime we only get results for those articles that have a category.
If we change these to left joins then we get all the articles.
Those with categories and those without.
Note how some data is repeated.
For example this article the peers twice because it's related to more than one category.
I'll sell.
This category appears more than once because it's related to more than one article.
We can get the data for one article by adding a where clause on the article.
If the article has no categories then we get one rijkaard in the resultset.
If we change the article idae to one that has more than one category.
When we execute this esky Well we get multiple Ricardos with the article details repeated but one row
for each category.