Database Design
Design a database schema using dbdiagram.io and generate codes automatically.
This short tutorial will help you learn how to design and create an SQL DB schema using dbdiagram.io. We will also learn how to save and share DB diagrams, then how to generate code to create the schema in a target database engine.
Let’s get to it. The picture below is the interface you will see when you visit dbdiagram.
The left hand side is where you design your DB schema, the right hand side shows you a graphical representation of your design including relationships that you can share with your team. You can use the export menu on the top bar to generate a code for your design in different variants including PDF, PNG, SVG, generate SQL, Postgres and SQL-server codes. The share button allows to generate a shareable link to your design, an embed link and an option to invite someone via their email address.
If you already have an SQL code and wants see a graphical representation or you just want to redesign it, the import button allows to import SQL codes of different databases including — schema.rb — files from Rails. There’s also a history button to check previous saved versions of your designs.
Designing a database Schema
The left hand side is where we design the db schema. Dbdiagram has a default design once you open the app. The design has three tables and their respective fields with a corresponding diagram in the right. We will create a simple savings app database to learn the database markup language used in dbdiagram. The first thing is to give our project a name, I’ll name it Simple savings.
We will have two tables namely the Users, Withdrawals and Savings tables. To design a database in dbdiagram, we use the DBML — Database Markup Language, you can check out the docs here for more details.
The image above show a complete design of our database. We have three tables with fields. We can create timestamps and give default values to the fields. The DBML allows us to build complex database schema and it updates our changes in real time. Below is a diagram generated for our design that i can share with my team.
When ever you finish writing on the left hand side, click on the auto arrange button on the bottom panel to arrange the diagram to fit your screen. Designing databases can always be a dilemma but with this tool, it can be a flex. You can also manually rearrange the diagram what best works for you. The image below shows a rearranged version of our design show the relationships and references better.
Now we can generate and download postgres code of our design using the export button and use it in our project.
Image below show the code generated for MySql database. You can see the use of back-ticks instead of quotes.
The image belows the generated code for Sql Server.
You can also generate PNG and PDF versions of your design.
To get updates on other articles, follow me on medium or twitter.
