A many-to-many reference can relate one record in either table to many records in the other table.
The only way to create many-to-many (N:M) reference between two tables in MySQL is using an intermediate table, which records set association between primary keys of first table and primary keys of second table.
Database Designer for MySQL helps you set many-to-many reference between two tables easily:
| ▪ | it automatically creates intermediate table with proper columns and indexes; |
| ▪ | and then creates regular references between columns of intermediate table and primary keys of tables being linked. |
Warning:
Remember, that tables being linked must have primary key.
How to create...
To create a many-to-many reference between two tables, click the N:M Reference (
) item on the Palette tab of Ribbon. Your mouse cursor will change its appearance. Then consistently click two tables you want to link with many-to-many reference.
Please note:
By default Database Designer for MySQL draws references automatically using shortest line between table shapes on diagram with reference's label placed in the center of this line. Sometimes this can lead to references crossing, or reference's label can fit below table shape. Starting from version 2.0 you can route references manually to avoid this. Take a look at Manual Reference Drawing topic.
See also:
Diagram Objects: References and Foreign Keys | Creating a Reference