Use of Plantuml Class Diagram for Relational Database Design

March 2015 · 2 minute read

I like to use plantuml for UML diagrams because it is faster to write UML in DSL with keyboard than to draw repititible rectangles with mouse. Plantuml is Java tool, easy installed and works on any java-supported platform where graphviz works. Internally plantuml uses graphviz to do all job for placing and drawing objects on the canvas.

From time to time I need to design structures for relational databases. Visualization or relational DB looks closely to UML class diagrams so I use plantuml for it purpose. Of course it will nice to automatically generate not only visual representation but also SQL instructions. Plantuml uses simple text format so it not difficult to convert it to SQL. I wrote simple script on Python for a generating MySQL DDL. It uses a notation for UML protected/public fields for hints where to create primary keys and indexes in SQL. So you may define PKs with # prefix in field name (it mean protected field in plantuml) and define index fields with + prefix (plantuml use if for public fields). Field type noted after field name as is. Also you may use one line comments after -- in the end of string. For example class definition:

@startuml

class dummy {
  Sample table.
  ==
  #id int(10)
  field1 int(10)
  field2 varchar(128)
}

@enduml

will be converted to SQL:

CREATE TABLE IF NOT EXISTS dummy (
  id               INT(10),
  field1           INT(10),
  field2           VARCHAR(128),
  PRIMARY KEY (id));

Script code in public domain: github.com/grafov/plantuml2mysql.

/database.png
UPD. Another way to use plantuml for database modeling:
plantuml.sourceforge.net/qa/?qa=331/database-modeling.