Database schema diagrams in 2022

Suggestions

• Whenever you publish a diagram, also include a comment explaining which software/website was used.

  • If you need to log into a website in order to update the diagram, post the login details… somewhere.

    • Ideally you would first create an account that is intended to be shared within you team.

• If the diagram was generated from source code, publish the source code (or a link to it) along with the diagram.

  • For bonus points, include this information in the image itself.

• These suggestions apply equally to email, chat, wiki, printouts, etc.

• Don’t send the source to clients, only to colleagues.

• Read The C4 model for visualising software architecture for a good system and suggestions on diagrams (then look at using plantUML with the C4 extentions).

  • This is a good talk on the topic:

---

Types of diagram

• Database schema diagram

• Flowchart

• Sequence diagram

• Component/Class diagram

• Code workflow

• …

---

Desktop software

Flowcharts

• VIsual-only

  • Dia - beware, it's Dia by name and dire by nature

  • Omnigraffle - Mac only

• Source + Visual

  • VS Code - has nice plantuml  integration - live preview

  • PlantUML (example)

    • Summary: Full featured language designed for visualising software architecture, build multiple diagrams from a single model, etc.

    • See also Structurizr

  • GraphViz

    • Summary: Simpler, general purpose language for diagrams (dot language)

Database

• …

---

Online software

Flowcharts

• ASCIIFlow

• Gliffy.com (now paid)

• moqups (has a free plan)

• PlantUML

  • PlantUML Webserver

  • Online Plant UML Editor

• Graphviz

  • Graphviz online

  • Edotor

  • Graphviz Visual Editor

Database

• diagrams.net (formerly draw.io)
• Quite heavy
• Like an online omnigraffle
• Doesn't specialise in databases
• No import function for SQL

• dbdiagram.io

• Free for up to 10 diagrams :-|
• Fairly good
• Has multiple import/export
• Importing is quite easy (although doesn't support all syntax)
• Diagrams don't look quite as good as dbdesigner.net
• dbdesigner.net
• Free for 2 diagrams? :-/
• Slightly better looking diagrams
• But more buggy (Login & save before starting!)
• Importing is more painful than dbdiagram.io

Hardware

• Pencil and paper - consider this for a first draft (scan/photo if necessary)

---

Comments

There are a number of ways to evaluate these software packages:

• Flowchart vs Database

• Online vs Desktop

• Visual-only vs Generated from source

• Source language: General (e.g. Dot) vs Specific (e.g. PlantUML + C4)

 

Source-generated diagrams have many benefits:

Dot is the easiest to learn, and PlantUML has the most features.

• Reply
• Edit
• Delete

There are online visual editors for both PlantUML and Graphviz! Best of both worlds

• 
  
• 
  

More PlantUML resources:

• Welcome to The Hitchhiker’s Guide to PlantUML! — The Hitchhiker's Guide to PlantUML documentation

• PlantUML Language Reference Guide

 

Note: PlantUML uses Graphviz to draw some diagrams. 

(sorry, the formatting of this post is a complete mess)

How to draw a histogram on linux

Generate a bar chart with Perl:

perl -lane 'print $F[0], "\t", "=" x ($F[1] / 5)' file

Adjust the number 5 to your desired width.

(source)

Automatically generated diagrams of Perl code

• Use UML::Sequence to diagram your method calls - Looks cool but couldn't get the module's tests to pass.
• Write your own code to produce a Graphviz spec file - Good choice if you want a data structure diagrammed, e.g. State transitions.
• SchemaSpy works well for database tables.

Untried:

• UML::Class::Simple - class diagrams, not methods?
• Devel::Diagram - class diagrams, not methods?
• Devel::DProfPP

Drawing software diagrams

Alternatives:

• Pencil and paper: Extremely easy to use, but no templates, and not online
• GraphViz: Good for automatically generating a diagram from code, but you need to implement a script to do it. Bad (impossible) for free-form manual drawing.
• Asciiflow: Good for quickly knocking up a rough drawing of boxes and lines. Bad for editing that drawing.
• OpenOffice/LibreOffice Draw: Seems to be good for diagrams. Can export to PDF. How do you make the canvas bigger?
• LucidChart: Free. Online only, but can export to PDF.
• Dia: Free, but clunky. Dia by name, dire by nature. Exported images are messed up.
• Visio: Microsoft. You must pay.
• Omnigraffle: Mac only, but nice.
• www.draw.io: Free, but not as intuitive as others.
• Gliffy.com: Free trial. Online only. Quite good.
• moqups.com: Good. One project per free account.
• Creately
• Diagramly?
• Pencil project?

Easily create graphs from code

Use graphviz:

1) write graph code

digraph g{
  Opened[label="1\nOpened\nE: open door"];
  Closed[label="2\nClosed\nE: closed door"];
  node[shape=plaintext];

  Opened -> close_door[arrowhead=none];
  close_door -> Closed;
  Opened -> open_door[dir=back];
  open_door -> Closed[arrowhead=none];
}


2) generate graph

dot -Tjpg -ograph.jpg graph.dot

3) view graph

You can easily store the source code to the graph in version control.
Don't forget to also store the instructions to rebuild it.