Database Modeling Tutorial Using PlantUML

In this tutorial, we’ll explore the database modeling aspect of a small blog using PlantUML. We’ll start by defining the tables and their attributes, then establish relationships between them based on the provided database model.

1. Understanding the Database Model

Let’s review the database model for our small blog:

Tables:

• Posts: Represents individual blog posts.
• Users: Stores user information for blog authors.
• Comments: Contains comments left by users on blog posts.
• Tags: Stores tags associated with blog posts.
• PostTags: Join table to establish a many-to-many relationship between posts and tags.

Relationships:

• Posts – Users: One-to-many relationship where a post belongs to one user.
• Posts – Comments: One-to-many relationship where a post can have many comments.
• Posts – Tags: Many-to-many relationship facilitated by the PostTags join table.
• Tags – Posts: Many-to-many relationship facilitated by the PostTags join table.

2. Creating the PlantUML Diagram

Now, let’s represent the database model using PlantUML:

@startuml
!define primary_key(x) <b><color:#b8861b><&key></color> x</b>
!define foreign_key(x) <color:#aaaaaa><&key></color> x
!define column(x) <color:#efefef><&media-record></color> x
!define table(x) entity x << (T, white) >>

table(Posts) {
  primary_key(id): UUID <<PK>>
  --
  column(title): VARCHAR(255) NOT NULL
  column(slug): VARCHAR(255) <<U>>
  column(content): TEXT
  column(excerpt): TEXT
  column(publish_date): DATETIME
  column(is_published): BOOLEAN
  foreign_key(author_id): UUID <<FK>>
}


table(Authors) {
  primary_key(id): UUID <<PK>>
  --
  column(username): VARCHAR(255) NOT NULL <<U>>
  column(email): VARCHAR(255) NOT NULL <<U>>
  column(password): VARCHAR(255)
}

table(Comments) {
  primary_key(id): UUID <<PK>>
  --
  foreign_key(post_id): UUID <<FK>>
  column(author_name): VARCHAR(255)
  column(author_email): VARCHAR(255)
  column(content): TEXT
  column(comment_date): DATETIME
}

table(Tags) {
  primary_key(id): UUID <<PK>>
  --
  column(name): VARCHAR(255) <<U>>
}


table(PostTags) {
  primary_key("post_id, tag_id"): UUID <<PK>>
  --
  foreign_key(post_id): UUID <<FK>>
  foreign_key(tag_id): UUID <<FK>>
}


Posts }|..|| Authors
note left of Authors: This is 1 to Many \n Posts.author_id <-> Authors.Ide
Comments }|..|| Posts
PostTags }|..|| Posts
PostTags }|..|| Tags

@enduml

After execution the diagram should look like the following

3. Code Explanation

Let’s break down the PlantUML code step by step:

Macros Definition:

• Primary Key Macro (primary_key): This macro defines the styling for primary key columns. It uses HTML formatting to display the key icon in bold and a specified color (#b8861b).Example: primary_key(id): UUID <<PK>>
• Foreign Key Macro (foreign_key): Similar to the primary key macro, this defines the styling for foreign key columns. It displays the key icon in a specified color (#aaaaaa).Example: foreign_key(author_id): UUID <<FK>>
• Column Macro (column): This macro defines the styling for regular columns. It displays a media-record icon in a specified color (#efefef).Example: column(title): VARCHAR(255) NOT NULL
• Table Macro (table): This macro defines the styling for tables. It sets the background color to white.Example: table(Posts) { ... }

Table Definitions:

• Posts Table (Posts): This table represents blog posts. It contains columns for primary key (id), title (title), slug (slug), content (content), excerpt (excerpt), publish date (publish_date), and a boolean flag for publication status (is_published). The author_id column is a foreign key referencing the Authors table.
• Authors Table (Authors): This table stores information about blog authors. It includes columns for primary key (id), username (username), email (email), and password (password). Both username and email are marked as unique to ensure data integrity.
• Comments Table (Comments): This table contains comments left by users on blog posts. It includes columns for primary key (id), foreign key referencing the Posts table (post_id), author name (author_name), author email (author_email), content (content), and comment date (comment_date).
• Tags Table (Tags): This table stores tags associated with blog posts. It includes columns for primary key (id) and tag name (name). The name column is marked as unique to ensure each tag is unique.
• PostTags Table (PostTags): This table serves as a join table to establish a many-to-many relationship between posts and tags. It includes composite primary key (post_id, tag_id) and foreign keys referencing the Posts and Tags tables, respectively.

Relationships:

• Posts – Authors: This relationship indicates that each post is authored by one author (one-to-many). The author_id column in the Posts table references the id column in the Authors table.
• Posts – Comments: This relationship signifies that each post can have multiple comments (one-to-many). The post_id column in the Comments table references the id column in the Posts table.
• Posts – Tags: This relationship represents a many-to-many relationship between posts and tags facilitated by the PostTags join table.
• Tags – Posts: This relationship mirrors the many-to-many relationship between posts and tags, also facilitated by the PostTags join table.

Conclusion:

In this tutorial, we’ve explored the PlantUML code for modeling the database structure of a blog. By understanding the macros, table definitions, and relationships within the code, you’ll be better equipped to visualize and customize your database model.