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.
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.
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?
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
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)
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.
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
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!
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.
