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.
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.
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
Generate DSL Code using 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.
- Run his SQL script, boom, you get the whole diagram DSL.
- Copy and paste it into dbdiagram.io, boom, you get your diagram.
It works like a charm!
We run the code in holistics.io, our product which has a powerful query editor, we set the
table_schema = "ecommerce"
We copy all the values to dbdiagram.io, tada!
Other dbdiagram.io resources
Subscribe to Holistics Blog
Get the latest posts delivered right to your inbox
From SQL Queries To Beautiful Charts
Connect to your database and build beautiful charts with Holistics BILearn More
"Holistics is the solution to the increasingly many and complex data requests from the operational teams"
Tang Yee Jie
Senior Data Analyst, Grab