關聯式資料庫設計必備 - dbdiagram.io ERD 繪圖工具 | Antonio's blog

什麼是 Entity-relationship diagram(ERD)？

ERD 是一種用來表示資料表之間關係的圖表，能夠描述一對一、一對多、多對一等關聯。透過 ERD，可以快速瞭解每張資料表的欄位定義及屬性，方便進行資料庫設計與管理。

【用戶購買定單實體關聯圖示意圖】

dbdiagram.io - 基本介紹

dbdiagram.io 是一個可以快速建置實體關聯圖的線上工具，能夠幫助您快速建立實體關聯圖(ERD)。它使用 Database Markup Language(DBML)語法來定義資料表結構和關聯，使用者可以直觀地設計資料表並即時調整它們之間的關係，讓修改立即反映在圖表中。

Database Markup Language(DBML)語法本身是一種 open-source DSL language

本篇文章主要會針對dbdiagram.io free plan 的方案能使用的功能進行介紹(免費仔 (๑•̀ω•́)ノ )

dbdiagram.io 優點

減少撰寫 SQL 時間：dbdiagram.io 支援將寫好的 DBML 語法轉換成對應的 Relational Database Management System（RDBMS） SQL DDL 語法，例如：PostgreSQL、MySQL、SQL Server、Oracle SQL 等。
- 透過【工具列】中的Export 能選擇要匯出的資料庫類型，但強烈建議還是要自己檢查一次。
團隊 ERD 即時共享：透過share功能即時分享現有 ER 圖表及相關 DBML 欄位定義及註解說明。
dbdocs 線上文件分享：dbdocs 是一款能透過 dbdiagram.io 中 DBML 一鍵生成的線上文件工具，能讓使用者快速查看每張資料表的定義、定義及說明。其最大的優勢在於可以瀏覽單一資料表跟其他資料表的關係，避免因為複雜的資料表關係而無法有效查看到重點資訊。
- 透過【工具列】中的Publish to dbdocs 可以根據現有的DBML語法轉換成dbdocs文件，建議團隊討論上可以透過dbdocs功能，關聯表顯示上較為清楚且可以設置密碼，提高分享的安全性。
圖檔靜態文件匯出：可以選擇將單一資料表與其他資料表之間的 ERD 匯出或所有資料表的 ERD 匯出成 PDF 或 PNG。
- 透過【工具列】中的Export 能選擇要匯出全部 ERD 成PDF或PNG，匯出的文件上會有dbdiagram.io 的圖標。
- 若想要針對單一資料表與其他資料表之間的 ERD 需要透過 dbdocs 才可以達成。

專案定義(Project Definition)

單行註解寫法：

Project 用戶購物紀錄專案 {
  database_type: 'PostgreSQL'
  Note: '紀錄用戶購買項目大綱及明細'
}

Note 支援 md 語法

多行註解寫法：

Project 用戶購物紀錄專案 {
  database_type: 'PostgreSQL'
  Note: '''
  # 紀錄用戶購買項目大綱及明細
  - users：用戶資料表
  - orders：訂單資料表
  - products：產品資料表
  - order_items：產品明細資料表
  '''
}

【 dbdocs 專案註解顯示方式】

如果要開啟dbdocs，記得點選【工具列】中的Publish to dbdocs，如果忘記工具列位置可以往上看

資料表定義(Table Definition)

預設寫法(使用public schema)：

1
2
3

Table table_name {
  column_name column_type [column_settings]
}

指定 schema 名稱 DBML 寫法：

1
2
3

Table schema_name.table_name {
  column_name column_type [column_settings]
}

【 dbdocs 使用預設與未使用指定名稱 schema 差別】

指定 schema 名稱優點：

用途分類：可以根據不同的用途或功能將資料庫物件分隔到不同的 schema 中，使資料庫結構更清晰、易於管理。

避免名稱衝突：不同的 schema 可以包含名稱相同的資料表，從而避免因名稱重複而產生的衝突。

權限管理：能針對不同的 schema 設定專屬的存取權限

資料表別名(Alias)

1
2
3

Table very_long_user_table as U {
  ...
}

資料表欄位關聯鍵寫法：Ref: U.id < posts.user_id(一對多的關係)

使用別名可以用於建立外來鍵關係時使用，特別是資料表名稱比較長的時候 (=´ω`=)。

資料表註解(Note)

Table users {
  id integer
  status varchar [note: 'status']

  Note: 'Stores user data'
}

Note 的規則均分成單行註解跟多行註解的方式

【 dbdocs 資料表註解顯示方式】

欄位定義(Column Definition)

Table buildings {
  id integer [ pk, unique, default: 123, note: 'Number' ]
  address varchar(255) [unique, not null, note: 'to include unit number']
  ...
}

欄位定義的格式：column_name column_type [column_settings]

column_name：欄位名稱
column_type：支援各種資料型別
column_settings：設定欄位屬性
- primary key or pk：主鍵
- null or not null：不得為空
- unique：唯一值的特性
- default：預設值(數字、字串、布林或表達式)
  
  表達式範例：now() - interval '5 days'(當前時間往前推五天)
- increment：自動遞增

【 dbdocs 欄位定義顯示方式】

索引定義(Index Definition)

Table bookings {
  id integer
  country varchar
  booking_date date
  created_at timestamp

  indexes {
    // 單欄索引
    created_at [name: 'created_at_index', note: 'Date'] // 單一欄位索引，用於針對 created_at 的查詢加速

    // 複合索引
    (country, booking_date) [unique, name: 'country_booking_idx'] // 唯一複合索引，用於加速 country 和 booking_date 的查詢
  }
}

索引格式：將欄位設置為索引可設定的參數為 index-name、index-type、index-setting 及 note

index-setting：可以設定unique及pk屬性。

index-type：僅接受hash及btree的選項，取決於資料庫的選擇。

note：區分成單行註解跟多行註解兩種方式

【 dbdocs 欄位定義顯示方式】

索引主要的原理在於寫入的時候，根據索引的欄位另外拉一張表儲存索引的欄位值。檢索的時候，可以根據儲存的索引進行掃描，加速檢索資料的時間。但同時也會增加寫入及更新的時間，因此在設置上需要根據實際檢索情境進行設計，以達到性能平衡。

索引大致上可以分成單欄索引及多欄索引(composite index)：

單欄索引：用於提升單個欄位的查詢性能，例如：created_at 欄位過濾或排序的操作。
多欄索引：用於優化涉及多個欄位的查詢。例如：在查詢中常用WHERE country = 'USA' AND booking_date = '2024-11-07'。

【 index 搭配屬性使用方式】

複合主鍵：(country, booking_date) [pk]

複合唯一索引：(country, booking_date) [unique]

Foreign Key Definitions(外來鍵定義)

外來鍵用於表示當前資料表與另一張資料表主鍵的關聯，建立兩者之間約束關係。用於確保兩張資料表的資料具有一致性，且參照的欄位值確實存在，從而維護資料的正確性。撰寫方式可以主要分成Long form、Short form兩種寫法，且可以設定Relationship settings。

// Long form
Ref name_optional {
  schema1.table1.column1 < schema2.table2.column2
}

// Short form:
Ref name_optional: schema1.table1.column1 < schema2.table2.column2

外來鍵關係：

一對多：users.id < posts.user_id
多對一：posts.user_id > users.id
一對一：users.id - posts.user_id

外來鍵變更處理機制

在實際使用情境中，當涉及外鍵關聯的父表發生變更(如刪除或更新)時，可以根據不同的操作行為來決定依賴該外鍵的子表應如何處理。

1	Ref: products.merchant_id > merchants.id [delete: cascade, update: no action]

參考操作(referential actions)：

cascade：當對應的父表記錄被刪除或更新時，自動刪除或更新所有引用該記錄的子表記錄。
restrict：不允許刪除或更新有引用的父表記錄。如果子表中存在對應的記錄，則拒絕此操作。(立即檢查)
set null：當對應的父表記錄被刪除或更新時，將子表中引用的外鍵設置為 NULL。
set default：當對應的父表記錄被刪除或更新時，將子表中引用的外鍵設置為預設值。
no action：不執行任何操作。如果子表中有引用，則拒絕刪除或更新父表記錄。(延遲檢查)

Mysql 中 no action / restrict 效用相似

Enum Definition(枚舉定義)

允許定義一組可選值的選項，確保使用者存入符合定義的選項值。

【枚舉定義 - 預設使用 public schema 】

Enum grade {
  "A+" [note: '優等']
  "A" [note: '好']
  "A-" [note: '中下']
  "Not Yet Set" [note: '尚未填寫']
}

【資料表欄位使用枚舉當作選項搭配 public schema 】

Table students {
  id integer [pk, unique, note: "Student ID"]
  name varchar(100) [not null, note: "Student name"]
  grade grade [note: "Student grade"]
}

【 dbdocs enum fields 呈現方式(可以看到 Enum 註解) 】

【 dbdocs table references 呈現方式(可以看到 Enum 選項值) 】

如果有指定schema名稱的情況下，枚舉定義跟資料表欄位使用要加上schema名稱前綴。

【使用 schema1 name 枚舉寫法呈現 - schema1.grade 】

Enum schema1.grade {
  "A+" [note: '優等']
  "A" [note: '好']
  "A-" [note: '中下']
  "Not Yet Set" [note: '尚未填寫']
}

【資料表欄位使用 schema1 name 寫法呈現 - schema1.grade 】

Table students {
  id integer [pk, unique, note: "Student ID"]
  name varchar(100) [not null, note: "Student name"]
  grade schema1.grade [note: "Student grade"]
}

dbdocs 生成特定資料表分享連結(Embedding Links beta feature)

當需要針對單一專案流程說明所使用的資料表時，可以嘗試使用 dbdocs 的 Embedding Links。這項功能允許自由選擇特定資料庫中的資料表，快速呈現並保存為視圖。不過，需要注意的是，此功能目前仍處於 beta 階段，且資料欄位的更新不會自動同步至已建立的 Embedding Links。

【 dbdocs Embedding Links 創建流程】

【 dbdocs 生成特定資料表分享連結流程】

回顧今天的學習內容

透過dbdiagram.io這個工具，我們可以更有效率地進行資料庫設計工作。它不僅提供了直覺的介面來繪製ERD圖表，更重要的是能夠通過DBML語法定義資料表之間的關係。對於團隊協作來說，能夠即時共享ERD設計和產生清晰的文件是非常實用的功能。

資料來源

DBML - Full Syntax Docs