Tour TaskFlow schema — đọc DDL hiểu vì sao chọn từng quyết định

TL;DR: Schema TaskFlow gồm 5 bảng: users (account), projects (container công việc), project_members (M:N junction), tasks (đơn vị công việc), comments (weak entity). Mỗi quyết định DDL ẩn một nguyên lý: khóa tự tăng 8-byte tránh wraparound; timestamp timezone-aware tránh lệch múi giờ; NOT NULL vs nullable FK encode business rule ("task phải có project, nhưng không bắt buộc có assignee"); composite PK trên junction table đảm bảo uniqueness M:N. Schema này tiến hoá theo từng module — bắt đầu đơn giản, thêm trigger/index/constraint khi bài toán thực sự đòi hỏi.

Bây giờ mở DDL ra đọc từng dòng: vì sao primary key dùng khóa tự tăng 8-byte không phải 4-byte, vì sao timestamp cần timezone context, vì sao project_members cần composite primary key. Mỗi quyết định là một bài học nhỏ ẩn trong vài ký tự.

Schema này chạy xuyên suốt khoá. Đọc kỹ một lần ở đây sẽ giúp bạn không phải quay lại đọc lại khi gặp từng table ở các bài sau.

1. Analogy — Schema vs bản vẽ blueprint phòng

Bản vẽ kiến trúc không chỉ vẽ hình dạng phòng — nó ghi tên từng phòng, kích thước tường, loại vật liệu, và quy tắc an toàn. DDL hoạt động theo cùng cách đó.

2. Tour 5 bảng — overview

erDiagram
  users ||--o{ projects : owns
  users ||--o{ project_members : member_of
  users ||--o{ tasks : assigned_to
  users ||--o{ comments : authored
  projects ||--o{ project_members : has
  projects ||--o{ tasks : contains
  tasks ||--o{ comments : has

3. users — anatomy của một table

CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- 8-byte auto-increment; BIGSERIAL (PG), AUTO_INCREMENT BIGINT (MySQL), IDENTITY BIGINT (SQL Server)
  email      VARCHAR(255) UNIQUE NOT NULL,
  name       VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP  -- luu UTC; tz-aware tuy engine: TIMESTAMPTZ (PG), TIMESTAMP normalize UTC (MySQL)
);

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Khóa tự tăng có hai lựa chọn về kích thước: 4-byte (INT, tối đa ~2,1 tỷ) và 8-byte (BIGINT, tối đa hơn 9 triệu tỷ). Hệ thống với vài triệu row tạo mới mỗi ngày có thể chạm giới hạn 4-byte sau vài năm — khi sequence bị wraparound, mọi INSERT sau đó thất bại. 8-byte loại trừ rủi ro này hoàn toàn. Cú pháp chuẩn SQL:2003 là GENERATED ALWAYS AS IDENTITY; các engine dùng shorthand riêng (BIGSERIAL trong PostgreSQL, AUTO_INCREMENT trong MySQL). Vì sao không UUID? Module 5 của khoá này sẽ phân tích tradeoff chi tiết: UUID tránh id collision trong distributed system nhưng làm B-tree index phân mảnh vì tính ngẫu nhiên.

email VARCHAR(255) UNIQUE NOT NULL

VARCHAR(n) là kiểu chuỗi chuẩn ANSI, portable giữa mọi RDBMS. UNIQUE tạo implicit B-tree index trên column này — mỗi email chỉ xuất hiện một lần, và lookup theo email sẽ dùng index thay vì full scan.

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

CURRENT_TIMESTAMP là hàm chuẩn ANSI, có ở mọi RDBMS. Điểm quan trọng: khi hệ thống có nhiều server ở múi giờ khác nhau hoặc user ở nhiều timezone, nên lưu timestamp theo UTC — và khi truy vấn, chuyển đổi về timezone hiển thị. Một số engine có kiểu timezone-aware tường minh: PostgreSQL dùng TIMESTAMPTZ; MySQL tự chuẩn hoá kiểu TIMESTAMP về UTC khi lưu (còn DATETIME thì không gắn timezone). Engine khác yêu cầu convention lưu UTC trong TIMESTAMP và xử lý conversion ở application layer.

4. projects + project_members — M:N pattern

CREATE TABLE projects (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  owner_id   BIGINT NOT NULL REFERENCES users(id),
  name       VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Pattern giống users: 8-byte auto-increment PK, VARCHAR cho string, TIMESTAMP cho thời gian. Điểm khác biệt: owner_id BIGINT NOT NULL REFERENCES users(id) — FK về users. Kiểu dữ liệu FK phải khớp với PK của table tham chiếu (BIGINT), không dùng cú pháp auto-increment (GENERATED AS IDENTITY / BIGSERIAL) vì FK chỉ lưu giá trị tham chiếu, không cần sequence.

CREATE TABLE project_members (
  user_id    BIGINT NOT NULL REFERENCES users(id),
  project_id BIGINT NOT NULL REFERENCES projects(id),
  role       VARCHAR(20) NOT NULL CHECK (role IN ('owner', 'member', 'viewer')),
  joined_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, project_id)
);

Đây là junction table thể hiện quan hệ M:N giữa users và projects: một user có thể tham gia nhiều project, một project có thể có nhiều user.

CHECK (role IN ('owner', 'member', 'viewer'))

Constraint inline kiểu enum cho 3 giá trị cố định — chuẩn ANSI, chạy trên mọi RDBMS. Alternative là CREATE TYPE role_enum AS ENUM (...) (PostgreSQL) hoặc ENUM('owner','member','viewer') (MySQL) — tiện hơn khi cùng tập giá trị đó được dùng ở nhiều table. Khoá này dùng CHECK vì portable và role chỉ xuất hiện ở một chỗ.

PRIMARY KEY (user_id, project_id)

Composite primary key đảm bảo một user chỉ xuất hiện một lần trong một project. Đây là pattern chuẩn cho mọi M:N junction table: PK gồm hai FK column, không cần thêm column id riêng (điều đó sẽ tạo surrogate key không cần thiết và mất constraint uniqueness ngầm định).

5. tasks — entity với optional FK và check constraint

CREATE TABLE tasks (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  project_id  BIGINT NOT NULL REFERENCES projects(id),
  assignee_id BIGINT REFERENCES users(id),
  title       VARCHAR(500) NOT NULL,
  status      VARCHAR(20) NOT NULL CHECK (status IN ('todo', 'doing', 'done', 'archived')) DEFAULT 'todo',
  due_at      TIMESTAMP,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

assignee_id BIGINT REFERENCES users(id) — không có NOT NULL

Task chưa gán cho ai là trạng thái hợp lệ — unassigned task. So sánh với project_id BIGINT NOT NULL: task phải thuộc về project, nhưng không bắt buộc phải có assignee. Sự khác biệt NOT NULL vs nullable FK này ảnh hưởng trực tiếp đến query: WHERE assignee_id = 5 chỉ trả về task đã gán, WHERE assignee_id IS NULL trả về task chưa gán.

due_at TIMESTAMP — không có DEFAULT

Không phải task nào cũng có deadline. Column nullable không có default nghĩa là due_at IS NULL = không có deadline. Khác với created_at và updated_at luôn phải có giá trị tại mọi thời điểm.

updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Hiện tại updated_at chỉ được điền tại INSERT — giống created_at. Để tự cập nhật khi có UPDATE, cần trigger. Module 7 (Transactions & consistency) của khoá này sẽ đi sâu vào trigger pattern đó. Đây là ví dụ schema tiến hoá theo nhu cầu thực tế thay vì cố gắng làm đúng hết ngay từ đầu.

6. comments — weak entity

CREATE TABLE comments (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  task_id    BIGINT NOT NULL REFERENCES tasks(id),
  user_id    BIGINT NOT NULL REFERENCES users(id),
  body       TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

task_id BIGINT NOT NULL REFERENCES tasks(id)

Comment không tồn tại nếu không có task — đây là weak entity. Lưu ý: DDL hiện tại chưa có ON DELETE CASCADE. Khi xoá một task đang có comment, hệ quản trị sẽ block thao tác đó với lỗi FK violation thay vì tự xoá comment theo. Module 5 của khoá này sẽ phân tích tradeoff: thêm ON DELETE CASCADE tiện lợi nhưng có thể xoá dữ liệu không phục hồi được nếu dùng không cẩn thận.

user_id BIGINT NOT NULL — khác với tasks.assignee_id

Comment phải có author — không có comment vô danh. So sánh với tasks.assignee_id cho phép NULL: business rule khác nhau → constraint khác nhau. Đọc DDL để hiểu business rule ẩn sau từng NOT NULL.

7. Schema tiến hoá xuyên 12 module

Schema TaskFlow không static — nó thay đổi theo từng module khi khoá học đi vào các chủ đề sâu hơn. Bảng dưới cho thấy big picture:

Pattern then chốt: bắt đầu đơn giản, tiến hoá theo nhu cầu thực tế. Không over-engineer ở ngày đầu tiên — thêm JSONB, vector, hay partition khi bài toán thực sự đòi hỏi.

8. Applied — dùng schema này như reference

Trong 80 bài tới, mỗi khi viết SQL example, schema TaskFlow là context ngầm. Có hai cách giữ reference này luôn sẵn:

Cách 1 — Bookmark bài này:

5 CREATE TABLE đầy đủ ở section 3–7 của bài này. Mở tab riêng để tham chiếu khi học M02 trở đi.

Cách 2 — Dùng DBeaver ER diagram:

Mở DBeaver → kết nối database → Database Navigator → chọn schema → click phải → "View Diagram". DBeaver tự tạo ER diagram từ FK có sẵn — có thể kéo thả bảng, thấy quan hệ trực quan.

Cross-reference với khoá Spring:

Nếu bạn đang học song song khoá Spring Data JPA của OLHub, Module 4 của khoá đó dùng cùng domain TaskFlow. Cùng một schema được nhìn từ hai góc: SQL thuần (khoá này) và JPA entity mapping (khoá Spring). Học cả hai giúp thấy rõ hơn sự tương ứng giữa DDL và Java entity.

9. Deep Dive — Schema design fundamentals

10. Liên hệ các bài khác

• Bài 03 — ER modeling: schema TaskFlow được thiết kế ở đó trước khi gõ DDL — bài này là bước tiếp theo đọc DDL thực tế.
• Bài 05 — SQL flavor map: các engine biểu đạt auto-increment và timestamp timezone-aware khác nhau — bài đó map rõ sự khác biệt.
• Module 5 — Thiết kế schema: normalization, ON DELETE CASCADE, và schema evolution — schema TaskFlow sẽ được mở rộng ở đó.
• Module 6 — Storage & indexing: UNIQUE constraint trên email tạo implicit B-tree index — cơ chế đó được mổ sâu ở Module 6.

11. Tóm tắt

• 5 bảng TaskFlow: users (account), projects (container công việc), project_members (M:N junction), tasks (đơn vị công việc), comments (weak entity của tasks).
• Khóa tự tăng 8-byte: tránh wraparound ở giới hạn ~2,1 tỷ của 4-byte INT — dùng BIGINT làm mặc định cho mọi PK mới (cú pháp: GENERATED AS IDENTITY chuẩn SQL, BIGSERIAL trong PG, AUTO_INCREMENT trong MySQL).
• Timestamp timezone-aware: lưu theo UTC, convert về timezone hiển thị ở query/app layer — tránh lệch 7-12 giờ khi deploy multi-region.
• M:N qua junction table với composite PK: project_members với PRIMARY KEY (user_id, project_id) — pattern chuẩn cho mọi quan hệ nhiều-nhiều.
• NULL có nghĩa: assignee_id nullable = unassigned hợp lệ; task_id NOT NULL trong comments = comment phải có task. Đọc NOT NULL như đọc business rule.
• Schema tiến hoá: trigger, composite index, constraint mở rộng — không cần có sẵn từ ngày đầu, thêm khi bài toán đòi hỏi.

12. Tự kiểm tra

CREATE TABLE customers (
id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- BIGSERIAL (PG) / AUTO_INCREMENT (MySQL)
email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE plans (
id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(100) NOT NULL
);

CREATE TABLE subscriptions (
customer_id BIGINT NOT NULL REFERENCES customers(id),
plan_id     BIGINT NOT NULL REFERENCES plans(id),
started_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (customer_id, plan_id)
);

Bài tiếp theo: Bài 05 — SQL flavor map — 8 điểm khác biệt giữa các engine