Some time ago, when I was designing a system module, I needed to add more than ten tables.

To design and define these dozens of tables quickly and easily, I found dbDiagram. IO, a tool that allows me to design database Relationship diagrams online and export DDL SQL.

Dbdiagram. IO is the community version of holistics. IO, a commercial product.

Dbdiagram. IO uses the DSL language to create database diagrams easily and quickly.

The interface is also very minimalist and design-friendly:

  • Sometimes we need to design tables in a relational database to implement our business functions.

  • Or we are not familiar with the table structure of a system and want to draw a graph to show the relationship between these entities.

  • Or we may want to convert the designed database diagram directly into DDL SQL.

  • And we don’t want to use complicated tools and pay high learning costs.

  • I also don’t want to use heavy tools and take up memory.

This is where the online database diagram tool comes in handy.

grammar

Here’s the syntax.

The syntax for defining a table is as follows:

Table users {
  id integer [pk]
  username varchar [not null, unique]
  full_name type [not null]
  .....
}
Copy the code

Aliases are also supported if the table name is too long:

Table longtablename as t_alias {
  .....
}
Copy the code

Defining a foreign key supports the following three relationships:

< : One-to-many
> : Many-to-one
- : One-to-one
Copy the code

There are three ways to define a foreign key:

Ref name-optional {
  table1.field1 < table2.field2
}

Ref name-optional: t1.f1 < t2.f2

Table posts {
  id integer [pk, ref: < comments.post_id]
  user_id integer [ref: > users.id]
}
Copy the code

example

The following uses several tables commonly used in e-commerce systems as examples to demonstrate its use.

Once you have logged into your Google account, you can save your graphics online, which can be accessed through a unique link: dbdiagram. IO /d/ 5CC9103ef…

Here is a DSL:

Table orders {
  id int [primary key]
  user_id int [not null, unique]
  status varchar
  created_at varchar
}

Table order_items {
  order_id int
  product_id int
  quantity int
}

Table products {
  id int [primary key]
  name varchar
  merchant_id int [not null]
  price int
  status varchar
  created_at varchar
  category_id int
}

Table users {
  id int [primary key]
  full_name varchar
  email varchar [unique]
  gender varchar
  date_of_birth varchar
  created_at varchar
  country_code int
}

Table merchants {
  id int [primary key]
  admin_id int
  merchant_name varchar
  country_code int
  created_at varchar

}

Table categories {
  id int [primary key]
  cat_name varchar
  parent_id int
}

Table countries {
  code int [primary key]
  name varchar
  continent_name varchar
}

Ref {
  orders.user_id > users.id
}
Ref {
  order_items.order_id > orders.id
}

Ref {
  order_items.product_id > products.id
}

Ref {
  products.merchant_id > merchants.id
}

Ref {
  products.category_id > categories.id
}

Ref {
  categories.parent_id > categories.id
}

Ref {
  users.country_code > countries.code
}

Ref {
  merchants.admin_id > users.id
}

Ref {
  merchants.country_code > countries.code
}
Copy the code

Here is the exported database diagram PDF:

conclusion

To summarize the features of dbdiagram. IO:

  • DSL: Database diagrams can be defined using a simple DSL language
  • Google Account: You can save your designs online using your Google Account
  • Online: No software installation is required. It is convenient and supports dragging and adjustment
  • Import/Export: Supports the Export of DDL SQL and PDF files and the Import of external data
  • Share: You can generate a Share link for team members to collaborate