ER modeling — bản vẽ trước khi gõ DDL

Bạn được giao "thiết kế DB cho TaskFlow". Mở DBeaver, gõ CREATE TABLE users ( — rồi đứng. Ai assign task cho ai? Task có nhiều assignee được không? Comment có thể nested không? Mỗi câu hỏi này là một quyết định thiết kế, không phải quyết định DDL. Gõ DDL trước khi trả lời những câu hỏi đó nghĩa là bạn đang xây nhà trước khi có bản vẽ — mọi thứ có vẻ nhanh lúc đầu, nhưng thay đổi sau đó rất tốn kém.

ER modeling (Entity-Relationship modeling) là bước trước khi gõ DDL — vẽ entity và relationship trên giấy hoặc Mermaid trước, gõ DDL sau. Bài này giải thích 4 khái niệm cốt lõi (entity, attribute, relationship, cardinality) qua TaskFlow schema và cách map ER diagram thành SQL DDL.

1. Analogy — Bản vẽ kiến trúc trước khi xây nhà

Kiến trúc sư vẽ blueprint trước khi thợ xây đổ móng. Blueprint không phải là nhà — nó là bản đặc tả các phòng, cửa nối, và cột chịu lực. Chỉ khi blueprint được review và chỉnh sửa trên giấy (miễn phí), thợ xây mới bắt đầu. Thay đổi cột chịu lực trên giấy tốn 5 phút. Thay đổi cột chịu lực sau khi đã đổ bê tông tốn hàng tuần và rất tốn kém.

ER diagram là blueprint của database — bạn vẽ entity và relationship trước, review logic, rồi mới sinh DDL.

2. Entity — danh từ cụ thể có ID riêng

Entity là bất kỳ "vật thể" hoặc "khái niệm" nào trong domain mà bạn cần lưu thông tin và có thể xác định duy nhất bằng một ID. Câu kiểm tra nhanh: nếu bạn cần đặt câu "tìm thing theo ID" thì thing có thể là entity.

Trong TaskFlow:

• users — entity vì mỗi user có ID riêng, tồn tại độc lập
• projects — entity vì mỗi project có ID riêng, tồn tại độc lập
• tasks — entity vì mỗi task có ID riêng, tồn tại độc lập

Strong entity vs weak entity:

Strong entity tự đứng được — tồn tại không phụ thuộc entity khác. users và projects là strong entity: xóa một project không làm user biến mất.

Weak entity phụ thuộc vào parent entity để tồn tại. comments trong TaskFlow là weak entity: comment tồn tại vì gắn với một task cụ thể. Xóa task, comment mất ý nghĩa — thậm chí nên xóa luôn (CASCADE DELETE).

-- Strong entity: ton tai doc lap
CREATE TABLE tasks (
  id         SERIAL PRIMARY KEY,
  title      TEXT NOT NULL,
  project_id INT  NOT NULL REFERENCES projects(id)
);

-- Weak entity: phu thuoc task, xoa task -> xoa comment theo
CREATE TABLE comments (
  id         SERIAL PRIMARY KEY,
  task_id    INT  NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  body       TEXT NOT NULL,
  author_id  INT  NOT NULL REFERENCES users(id)
);

3. Attribute — thuộc tính của entity

Attribute là thông tin mô tả entity. Mỗi entity có một tập attribute, mỗi attribute có tên và domain (kiểu dữ liệu). Có 4 loại attribute cần phân biệt:

Simple attribute — một giá trị atomic, không thể chia nhỏ hơn. Phần lớn attribute là loại này: email, name, status, created_at. Mỗi cái map thẳng thành 1 column.

Composite attribute — một giá trị phức hợp gồm nhiều sub-field. address là composite: gồm street, city, zip_code. Trong relational model, composite attribute thường được flatten thành nhiều column riêng:

-- Flatten composite attribute "address" thanh 3 column rieng
CREATE TABLE user_profiles (
  user_id  INT  PRIMARY KEY REFERENCES users(id),
  street   TEXT,
  city     TEXT,
  zip_code TEXT
);

Multi-valued attribute — một entity có nhiều giá trị cho cùng một attribute. Ví dụ: một task có nhiều tags. Trong relational model, multi-valued attribute vi phạm atomic value (1NF — sẽ học chi tiết trong Module 4 của khoá này), nên phải tách thành bảng riêng:

-- WRONG: luu tags nhu comma-separated string
-- vi pham 1NF, khong index duoc, khong query hieu qua
CREATE TABLE tasks (
  id    SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  tags  TEXT  -- 'bug,urgent,backend'
);

-- RIGHT: tach thanh bang rieng
CREATE TABLE task_tags (
  task_id INT  NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  tag     TEXT NOT NULL,
  PRIMARY KEY (task_id, tag)
);

Derived attribute — giá trị có thể tính từ attribute khác, không cần lưu. age tính được từ birth_date và ngày hiện tại. task_count của một project tính được từ COUNT trên bảng tasks. Trong relational model, derived attribute thường không lưu — tính lúc query:

-- Derived attribute: tinh luc query, khong luu column rieng
SELECT
  u.id,
  u.name,
  DATE_PART('year', AGE(u.birth_date)) AS age  -- derived, khong luu
FROM users u;

4. Relationship — cạnh nối entity

Relationship mô tả liên kết giữa hai entity. Trong ER diagram, relationship là cạnh nối hai entity node. Trong SQL, relationship được hiện thực bằng foreign key — hoặc junction table khi cần.

Có 3 loại cardinality:

1:1 (một — một) — mỗi instance của entity A liên kết với tối đa một instance của entity B, và ngược lại. Loại này hiếm trong production vì thường có thể gộp vào cùng một bảng. Ví dụ hợp lý: users ↔ user_profiles khi muốn tách dữ liệu nhạy cảm (bio, avatar URL) ra bảng riêng để kiểm soát truy cập.

-- 1:1 via unique foreign key
CREATE TABLE user_profiles (
  user_id   INT  PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  bio       TEXT,
  avatar_url TEXT
);
-- user_id vua la PK, vua la FK -> dam bao 1:1

1:N (một — nhiều) — loại phổ biến nhất. Một instance của entity A liên kết với nhiều instance của entity B. Ví dụ: một project có nhiều tasks, nhưng mỗi task chỉ thuộc về một project. Hiện thực bằng FK column ở phía "nhiều":

-- 1:N: project (1) -> tasks (N)
-- FK project_id dat tren bang "nhieu" (tasks)
CREATE TABLE tasks (
  id         SERIAL PRIMARY KEY,
  title      TEXT NOT NULL,
  project_id INT  NOT NULL REFERENCES projects(id) ON DELETE CASCADE
);

M:N (nhiều — nhiều) — mỗi instance của A liên kết với nhiều instance của B, và ngược lại. Ví dụ: một user có thể là member của nhiều projects, và một project có nhiều users. Loại này bắt buộc phải có junction table (bảng trung gian):

-- M:N: users <-> projects qua junction table project_members
CREATE TABLE project_members (
  user_id    INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  project_id INT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  role       TEXT NOT NULL DEFAULT 'member',
  joined_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (user_id, project_id)  -- composite PK dam bao unique
);

5. ER diagram TaskFlow — bức tranh đầy đủ

Kết hợp cả 5 entity và các relationship, ER diagram của TaskFlow trông như sau (Mermaid crow's foot notation):

erDiagram
  users ||--o{ projects : owns
  users ||--o{ project_members : "is member"
  projects ||--o{ project_members : has
  projects ||--o{ tasks : contains
  users ||--o{ tasks : "assigned to"
  tasks ||--o{ comments : has
  users ||--o{ comments : wrote

Giải thích từng cạnh:

• users ||--o{ projects — 1 user tạo/sở hữu nhiều project (1:N)
• users ||--o{ project_members và projects ||--o{ project_members — M:N qua junction table
• projects ||--o{ tasks — 1 project chứa nhiều task (1:N)
• users ||--o{ tasks — 1 user được assign nhiều task (1:N; một task có thể chỉ có 1 assignee trong schema đơn giản này)
• tasks ||--o{ comments — 1 task có nhiều comment (1:N, weak entity)
• users ||--o{ comments — 1 user viết nhiều comment (1:N)

6. ER → SQL DDL trong 5 phút

Từ ER diagram, apply 4 mapping rules để sinh DDL:

Áp dụng cho TaskFlow — full DDL từ ER diagram Section 5:

-- Strong entity: users
CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      TEXT   NOT NULL UNIQUE,
  name       TEXT   NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Strong entity: projects
CREATE TABLE projects (
  id          SERIAL PRIMARY KEY,
  name        TEXT NOT NULL,
  description TEXT,
  owner_id    INT  NOT NULL REFERENCES users(id),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Junction table: M:N between users and projects
CREATE TABLE project_members (
  user_id    INT  NOT NULL REFERENCES users(id)    ON DELETE CASCADE,
  project_id INT  NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  role       TEXT NOT NULL DEFAULT 'member',
  joined_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (user_id, project_id)
);

-- Strong entity: tasks (1:N from projects, 1:N from users)
CREATE TABLE tasks (
  id          SERIAL PRIMARY KEY,
  title       TEXT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'todo',
  project_id  INT  NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  assignee_id INT       REFERENCES users(id) ON DELETE SET NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Weak entity: comments (ON DELETE CASCADE theo task)
CREATE TABLE comments (
  id         SERIAL PRIMARY KEY,
  body       TEXT NOT NULL,
  task_id    INT  NOT NULL REFERENCES tasks(id)    ON DELETE CASCADE,
  author_id  INT           REFERENCES users(id)    ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Mỗi dòng DDL có thể trace ngược về một quyết định trong ER diagram. Đây là lý do vẽ ER trước: khi đồng nghiệp hỏi "sao project_members lại cần?", bạn chỉ cần chỉ vào cạnh M:N trong ER diagram, không cần giải thích từ đầu.

7. Pitfall — 3 lỗi phổ biến

Lỗi 1 — Quên junction table cho M:N: lưu member_ids như array column trên bảng projects.

-- WRONG: luu array user_id tren projects
-- query "user A thuoc project nao?" phai scan + parse array
CREATE TABLE projects (
  id         SERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  member_ids INT[]  -- WRONG: vi pham 1NF, khong index FK duoc
);

-- RIGHT: junction table
CREATE TABLE project_members (
  user_id    INT NOT NULL REFERENCES users(id),
  project_id INT NOT NULL REFERENCES projects(id),
  PRIMARY KEY (user_id, project_id)
);

Lỗi 2 — Nhầm 1:N với M:N dẫn đến over-normalize: thêm junction table khi quan hệ thực tế là 1:N. Ví dụ: nếu một task chỉ có đúng một assignee, tạo task_assignees junction table là thừa — chỉ cần assignee_id FK trên bảng tasks. Junction table chỉ cần thiết khi thực sự có M:N.

Lỗi 3 — Quên CASCADE cho weak entity: xóa task nhưng không CASCADE xóa comment → comment trở thành orphan row (FK violation nếu có constraint, hoặc dead row nếu không có constraint).

-- WRONG: thieu ON DELETE CASCADE cho weak entity
CREATE TABLE comments (
  task_id INT NOT NULL REFERENCES tasks(id)  -- thieu CASCADE
);
-- Xoa task -> comment van con, FK violation hoac dead data

-- RIGHT
CREATE TABLE comments (
  task_id INT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE
);

8. Applied — vẽ ER trước cứu 6 tháng debt

Một team backend bỏ qua bước ER, gõ DDL thẳng sau buổi kickoff 30 phút. Schema đầu tiên: projects(id, name, member_ids TEXT) — lưu user ID dưới dạng comma-separated string. Hệ thống chạy được với vài trăm user.

Sau 6 tháng, product yêu cầu thêm role cho từng member trong project (admin/member/viewer). Không thể thêm vào column member_ids TEXT được — phải migrate toàn bộ data sang junction table. Với hàng chục triệu row và zero downtime requirement, migration đó mất nhiều tuần lên kế hoạch và gây downtime đáng kể trong quá trình thực thi.

Team B trong cùng công ty dành 30 phút vẽ ER diagram trước, phát hiện M:N ngay trong buổi review. Junction table project_members có trong schema từ ngày đầu. Khi product yêu cầu thêm role, chỉ cần ALTER TABLE project_members ADD COLUMN role TEXT — zero downtime, xong trong 5 phút.

Tools vẽ ER: dbdiagram.io (free, export SQL), DrawIO (free, offline), Mermaid trong README/MDX (giống bài này — diagram sống cùng code).

Bonus: khi học bài M01.4 (cài Postgres local), DBeaver có tính năng ER reverse engineering — import schema từ database đang chạy và tự sinh ER diagram. Hữu ích khi tiếp nhận codebase cũ không có documentation.

9. Deep Dive — ER modeling foundations

10. Tóm tắt

• ER modeling là bước trước DDL — vẽ entity và relationship trước, sinh DDL sau. Thay đổi trên diagram miễn phí; thay đổi sau khi có data production rất tốn kém.
• Entity = danh từ trong domain có ID riêng, tồn tại độc lập (strong) hoặc phụ thuộc parent (weak entity → CASCADE DELETE).
• 4 loại attribute: simple (1 giá trị atomic), composite (nhiều sub-field → flatten), multi-valued (nhiều giá trị → tách bảng riêng, vi phạm 1NF), derived (tính lúc query, không lưu).
• 3 cardinality: 1:1 (hiếm, dùng khi tách concern), 1:N (phổ biến nhất, FK ở "many" side), M:N (bắt buộc junction table).
• M:N cần junction table — không dùng array column hay comma-separated string; junction table cho phép thêm attribute vào relationship và JOIN hiệu quả.
• Mapping rules ER → DDL: entity → CREATE TABLE, 1:N → FK column, M:N → junction table với composite PK, weak entity → ON DELETE CASCADE.
• Multi-valued attribute vi phạm 1NF — sẽ giải thích chi tiết trong Module 4 (Normalization) của khoá này.

11. Tự kiểm tra

CREATE TABLE user_follows (
follower_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
followee_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
followed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (follower_id, followee_id),
CHECK (follower_id <> followee_id)  -- khong tu follow chinh minh
);

Bài tiếp theo: Cài Postgres local — psql + GUI tool