關聯式資料庫設計必備 - dbdiagram.io ERD 繪圖工具

什麼是 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 優點

• 減少撰寫 SQL 時間：dbdiagram.io 支援將寫好的 DBML 語法轉換成對應的 Relational Database Management System（RDBMS） SQL DDL 語法，例如：PostgreSQL、MySQL、SQL Server、Oracle SQL 等。
• 團隊 ERD 即時共享：透過share功能即時分享現有 ER 圖表及相關 DBML 欄位定義及註解說明。
• dbdocs 線上文件分享：dbdocs 是一款能透過 dbdiagram.io 中 DBML 一鍵生成的線上文件工具，能讓使用者快速查看每張資料表的定義、定義及說明。其最大的優勢在於可以瀏覽單一資料表跟其他資料表的關係，避免因為複雜的資料表關係而無法有效查看到重點資訊。
• 圖檔靜態文件匯出：可以選擇將單一資料表與其他資料表之間的 ERD 匯出或所有資料表的 ERD 匯出成PDF或PNG。

專案定義(Project Definition)

單行註解寫法：

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

Note 支援 md 語法

多行註解寫法：

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

【 dbdocs 對應顯示區塊 - 專案註解 】

表單定義(Table Definition)

預設寫法(使用public schema)：

Table table_name {
  column_name column_type [column_settings]
}

指定 schema 名稱 DBML 寫法：

Table schema_name.table_name {
  column_name column_type [column_settings]
}

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

指定 schema 名稱優點：

• 用途分類：可以根據不同的用途或功能將資料庫物件分隔到不同的 schema 中，使資料庫結構更清晰、易於管理。
• 避免名稱衝突：不同的 schema 可以包含名稱相同的資料表，從而避免因名稱重複而產生的衝突。
• 權限管理：能針對不同的 schema 設定專屬的存取權限

表單別名(Alias)

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'
}

【 dbdocs 對應顯示區塊 - 表單註解 】

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

欄位定義(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-setting、note

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

【 index 搭配屬性組合使用方式】

• 複合主鍵：(country, booking_date) pk
• 複合唯一索引：(country, booking_date) unique

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

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

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

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

外來鍵變更處理機制

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

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+"
  "A"
  "A-"
  "Not Yet Set"
}

【 表單欄位使用 - 預設使用 public schema 】

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

如果有使用schema的情況下，定義跟使用要加上schema名稱前綴。

【 定義 - 使用 schema1 name 】

enum schema1.grade {
  "A+"
  "A"
  "A-"
  "Not Yet Set"
}

【 表單欄位使用 - schema1 name 】

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

回顧今天的學習內容

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