PlantUML for database modeling

5 min readJan 29, 2024

An introduction on a versatile database modeling documentation tool

What is UML and why I should use PlantUML?

Created at the end of the 90s, the UML is a reference standard for modeling and documenting software architectures. In particular, it enables the visualization of object structures and their relationships, and thus the efficient optimization of application design.

PlantUML, meanwhile, is an open source tool created in 2009 by a French developer named Arnaud Roque. It lets you create technical diagrams using a dedicated, intuitive language, and is available as a plugin in many IDEs.

What does this have to do with UML, you may ask? Well, PlantUML lets you create UML diagrams as code rather than strictly visually, which has several advantages:

Code is easier to maintain than drawn diagrams.
PlantUML draws and formats the diagrams from the code.
Code can be versioned using tools such as Github.
Numerous plug-ins exist in documentation tools and IDEs to generate diagrams.

Prerequisites for this tutorial

You’ll need some tools to render PlantUml code as images, the easiest way is to use the official online rendering tool.

There is also numberous plugins available on IDE’s marketplaces, like this one for Visual Studio Code.

Modeling a simple Table

Let’s move on to a concrete example. Let’s imagine I need to model a client table for an e-commerce site, with an: id, last name, first name, address, city and zip code.

Here’s how I’m going to describe my table

@startuml

' configuration
hide circle
skinparam linetype ortho

' comment
entity "client" as client {
 id: number
 --
 first_name: varchar2(128)
 last_name: varchar2(128)
 address: varchar2(256)
 city: varchar2(128)
 postal_code: varchar2(10)
}
note right
 <b>Note</b>
 This is the client table
end note

@enduml

Here’s how the language is structured:

In the manner of PHP, the script begins with a delimiter (@startuml), followed by an optional configuration block that lets you modify the appearance of the rendering.

Next comes the table structure itself (the double hyphen is an optional separator), and finally I’ve added a “note” block that lets you draw a comment to the right of the table.

Representing relationships with other tables

Well, I’ve created a customer table, but it obviously won’t be enough to manage my orders.

Let’s add new tables

So I’m going to create a new table to model my: orders, products ordered and I’ll take the opportunity to add a country field to my customer table.

@startuml

' configuration
hide circle
skinparam linetype ortho

' comment
entity "client" as client {
 id: number
 --
 first_name: varchar2(128)
 last_name: varchar2(128)
 address: varchar2(256)
 city: varchar2(128)
 postal_code: varchar2(10)
}
note right
 <b>Note</b>
 This is the client table
end note

entity "order" as order {
 person_id: number
 product_id: number
 --
 creation_date: date
}

entity "product" as product {
 id: number
 --
 name: varchar2(128)
 color: varchar2(128)
 quantity: number
}

entity "country" as country {
 id: number
 --
 name: varchar2(128)
}

@enduml

Setting relations between tables

Now that the table diagrams have been generated, we need to define the relationships between the tables:

A client is related to one country, a country can be related to one or many client
A client is related to zero or more orders, an order must be related to one client
An order must be related to one product at least and at least one client
A product is related to zero or more orders

Here’s how to represent these relationships by adding these lines just before the end delimiter (@enduml)

client ||--o{ command
order }o--|| product
client }o..|| country

Here’s the updated diagram. Note that PlantUML automatically places the diagram elements in relation to their relationships.

The notation of relationships between tables is based on the crowfoot convention. Here is a non-exhaustive list of available options syntax, with two flavors:

Identifying relationship
Non-identifying relationship

Adding colors and styles

PlantUML offers several options for highlighting tables or organizing a complex layout.

@startuml

' configuration
hide circle
skinparam linetype ortho
left to right direction

' gradient
!define TABLE_GRADIENT_BACKGROUND #F2F2F2-fcffd6

' couleur solide
!define NEW_TABLE_BACKGROUND #6fe890

skinparam class {
    BackgroundColor TABLE_GRADIENT_BACKGROUND
    BorderColor Black
    ArrowColor Blue
 FontSize 13
}

entity "client" as client {
 id: number
 --
 first_name: varchar2(128)
 last_name: varchar2(128)
 address: varchar2(256)
 city: varchar2(128)
 postal_code: varchar2(10)
}

entity "country" as country ##NEW_TABLE_BACKGROUND {
 id: number
 --
 name: varchar2(128)
}

client }o..|| country

@enduml

In this example, I’ve defined the following rules: entities will be generated from left to right, the font size will be set to 13xp, the default color for all tables will be a yellow-white gradient, links between entities will be in blue and some tables will be plain green.

Here’s how these parameters are defined:

left to right direction : this option tells PlantUML to draw elements from left to right and not from top to bottom.
!define TABLE_GRADIENT_BACKGROUND #F2F2F2-fcffd6 : declaration of the variable containing the gradient background color for all tables
define NEW_TABLE_BACKGROUND #6fe890 : declaration of variable containing solid background color for all new tables
the skinparam class block: this block contains the default configuration for all entities
entity “country” as country ##NEW_TABLE_BACKGROUND : via this method it is possible to override the entity background color

Grouping tables

When the schema becomes too large, or if it defines the tables of several projects, it’s useful to be able to group certain tables together for easier reading.

@startuml

' configuration
hide circle
skinparam linetype ortho

' commentaire
entity "client" as client {
 id: number
 --
 first_name: varchar2(128)
 last_name: varchar2(128)
 address: varchar2(256)
 city: varchar2(128)
 postal_code: varchar2(10)
}

entity "order" as order {
 person_id: number
 product_id: number
 --
 creation_date: date
}

entity "country" as country {
 id: number
 --
 name: varchar2(128)
}

package "Product data" as product_data {
 entity "product" as product {
  id: number
  --
  name: varchar2(128)
  color: varchar2(128)
  quantity: number
 }

 entity "size" as size {
  id: number
  --
  name: varchar2(128)
  color: varchar2(128)
  quantity: number
 }
 note bottom
  Comment on table
 endnote

 entity "product_sizes" as product_sizes {
  product_id: number
  size_id: number
 }
}
note bottom
 Comment on group
endnote

client ||--o{ order
order }o--|| product
client }o..|| country

product }|--|| product_sizes
size }|--|| product_sizes

@enduml

Further customization

There are still many configuration options to discover: integrations with other schematics, colors, layouts, etc…

To find out more, visit the official documentation:

Entity Relationship diagram syntax and features : https://plantuml.com/fr/ie-diagram