[Tutorial] Many-to-many Relationships

[huy]

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

Update Jun 2022: We have supported the native many-to-many relationtionship syntax. This post has been updated with the new example.

Ref: authors.id <> posts.id

Many-to-many Relationship

You can define a many-to-many relationship in two ways:

1. Directly define the many-to-many relationship with the native many-to-many relationship definition
2. Use two alternative one-to-many relationships with an associative (join) table

1. Use the native many-to-many relationship definition

You can directly define the many-to-many relationship, like:

Table books {
  id int [pk]
  ...
}

Table authors {
  id int [pk]
  ...
}

Ref books_authors: books.id <> authors.id // many-to-many

2. Use the associative (join) table

For example

We have 2 tables: Authors and Books. An Author can write several Books, and a Book can be written by several Authors

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

Many-to-many (data model)

[bmitchinson]

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

[WELZ]

@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]
}

[Germo_Gortz]

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.

[Jinghang_Hu]

Agree with Germo_Gorz. I don’t understand why many-to-many relationship is not supported? It is used in real life.

[Thi_Nguyen]

Since many users requested the many-to-many, zero-to-many relationships, we’ve already put it in our roadmap. We’ll keep you posted when it ready.

[nigelfloe]

Hi Thi, I’m vetting DBML and dbdiagram.io for a project, and many-to-many relationships would be a must-have. Do you have any updates on when this feature is expected to be available?

[huy]

Hi @Germo_Gortz, @Jinghang_Hu and @nigelfloe, thank you for your candid feedback, really appreciate it.

Could you please shed some light on what you’re thinking behind this feature? As far as I know, a many-to-many relationship can be defined as a two 1-to-many relationship. What would be the problem if you define it that way? Is it purely out of inconvenience?

If we support native many-to-many relationship definition, I imagine it will be something like:

Table books {
  id int [pk]
  name varchar
}
Table authors {
  id [pk]
  name varchar
}
Ref books_authors: books <> authors // many-to-many

Which is essentially equivalent to this:

Table books {
  id int [pk]
  name varchar
}
Table authors {
  id [pk]
  name varchar
}
Table books_authors {
  id int [pk]
  book_id int
  author_id int
}
Ref: books.id > books_authors.book_id
Ref: authors.id > books_authors.author_id

[nigelfloe]

Thanks for the response @huy

For our case, we’re trying to visualize a large complex schema that has a large number of many-to-many relationships and will not (necessarily) be implemented in a SQL database. The goal of using a tool such as DBML or dbdiagram for us would be to document and visualize business-relevant information about our data schema.

Adding a number of intermediate join tables to our visualization (such as the ‘books_authors’) table in your example, would only serve to clutter our visualization and documentation, and does not convey any additional business information.

This comment (not from myself0 on the DBML github repo does a good job explaining my thinking about requiring join tables to handle many-to-many relationships in DBML (and dbdiagram by extension)

[Question] Many-to-many relationships

From this perspective, join tables are essentially an implementation detail that don’t convey any essential business meaning. Also, requiring bridge table to handle many-to-many relationships is a SQL convention, and limits the utility of DBML and dbdiagram for users who are not using SQL databases.

[minhl]

I like the suggestion from @huy for many-to-many
Ref books_authors: books <> authors // many-to-many
And thank you to the team.

[huy]

Thank you for sharing this. I think I understand the concept better and agree this is useful. I’ve added a Feature Request here so that everyone can go in and upvote: Support many-to-many relationship in DBML

[nigelfloe]

thank you huy
really appreciate the help!

[Rajan_Rengasamy]

Hi all,

is there any movement around creating zero-to-many relationships?

[Thi_Nguyen]

Update June 2022: This feature has been developed and released. Please see below for the official announcement.

New Feature: Many-to-many Relationships

A much-requested feature, dbdiagram now supports Many-to-many Relationships! When drawing the many-to-many relationship, you no longer need to create an associative (join) table. Instead, just define it directly, such as: Table books { id int [pk] ... } Table authors { id int [pk] ... } Ref books_authors: books.id <> authors.id // many-to-many [many-to-many-relationships] For more DBML syntax updates, please check out our docs!

[Thi_Nguyen]

Not yet, Rajan.

The zero-to-many relationships are still in our consideration.

[Richard_Hall]

But Many-to-Many relationships ARE supported.

[madooei]

Wow! I just spent a good 20 minutes playing with this and writing my post with several screenshots, and here I’m not allowed to make it! I know this is a restriction in discourse, but you should have really turned it off! It is the very opposite of welcoming new users into your community!

My reply to this post is here if anyone cares to read it: Reply to [Tutorial] Many-to-many Relationships - Google Docs

[zenomeki]

This shorthand is all fine & dandy and is sure to help out a lot of projects but personally, I’m not going to use it as our many-to-many relation usecases involve a lot of metadatata on the relation itself.