/ dbdiagram, Data at Work

dbdiagram.io Many to Many Relationship and Diagram Generator SQL Script

by Anthony Thong Do

dbdiagram.io Many to Many Relationship and Diagram Generator SQL Script

In this post, we will be sharing with you two tips to make dbdiagram.io work better for you

  • How to define many to many relationships
  • How to auto-generate diagram from your current existing database with minimal effort using a SQL script

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.
Screen-Shot-2019-02-25-at-9.13.43-PM

Other Examples

E-commerce

An Order can have multiple Products, a Product can be ordered in multiple Orders
Screen-Shot-2019-02-25-at-9.18.07-PM

Students and Classes

A Student can enroll multiple Classes and a Class can be enrolled by multiple Students
Screen-Shot-2019-02-25-at-9.25.34-PM-1

Learn more

https://en.wikipedia.org/wiki/Many-to-many_(data_model)

Generate DSL Code using SQL

Author: @matthewoldham
Github link: https://github.com/matthewoldham/dbdiagram.io-ddl-queries/blob/master/sql/postgresql.sql

Last week, we found an amazing solution from Matt Oldham. He was having trouble with dbdiagram.io picking up primary/foreign keys when reverse engineering. So, he ended up writing a query to extract the schema DSL in just the right format.

  1. Run his SQL script, boom, you get the whole diagram DSL.
  2. Copy and paste it into dbdiagram.io, boom, you get your diagram.

It works like a charm!

For example

  1. We run the code in holistics.io, our product which has a powerful query editor, we set the table_schema = "ecommerce"
    Screen-Shot-2019-02-25-at-8.51.26-PM

  2. We copy all the values to dbdiagram.io, tada!
    Screen-Shot-2019-02-25-at-8.53.29-PM

Other dbdiagram.io resources

From SQL Queries To Beautiful Charts

Connect to your database and build beautiful charts with Holistics BI

Learn More
Grab Logo

"Holistics is the solution to the increasingly many and complex data requests from the operational teams"


Tang Yee Jie - Senior Data Analyst, Grab

Tang Yee Jie

Senior Data Analyst, Grab