[Tutorial] Many to Many Relationship

In this post, we will be sharing with you a tip on how to define many to many relationships in dbdiagram.

Many to Many Relationship

A lot of people ask for this feature, however, many-to-many is not a kind of relationship that we can present it on your diagrams directly. We need to create an associative (join) table instead of drawing the relationship directly.

For example

We have 2 tables: Authors and Books. An Author can write several Books, and a Book can be written by several Authors
Screen-Shot-2019-02-25-at-9.12.33-PM

Many Books are related to many Authors, however, it’s not possible to draw a single relationship line here since both tables don’t have anything to connect. In this case, to present this relationship, you need to create an associative table author_book to connect them.

Other Examples

E-commerce

An Order can have multiple Products, a Product can be ordered in multiple Orders

Students and Classes

A Student can enroll multiple Classes and a Class can be enrolled by multiple Students

Learn more

2 Likes

Can we see the code for these examples @huy , thanks!

@bmitchinson here’s a quick example

Table authors {
  author_id int
  name varchar
  dob datetime
  gender varchar
}

Table books {
  book_id int
  release_date datetime
  title varchar
}

Table author_book {
  author_id int [ref: > authors.author_id]
  book_id int [ref: > books.book_id]
}
7 Likes

your decision not to allow many-to-many relationships is not helpful. Because in real life they exist and they are one the most important features which I use for many years and in every model. And I need a way to document them. The example from the Wikipedia is very strange: A normal many-to-many relationship is between authors in one table and authors in the other table. There is no way to add a not existing third table into the documentation only to be able to document my models.

Maybe I need to explain that my final databases are analytical databases (Microsoft SSAS), and many-to-many relationships are supported, I love them, and I need to be able to document them. All this academic “we don’t want to allow you to document them because we don’t understand how they work” is contraproductive. I ask you: please just support them and allow to document the real existing life.

1 Like