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

Bài 4 đã apply schema TaskFlow — bạn đã có psql kết nối, 10 user trong bảng users, và database sẵn sàng để query. Bây giờ mở DDL ra đọc từng dòng: vì sao primary key dùng BIGSERIAL không INT, vì sao timestamp dùng TIMESTAMPTZ không TIMESTAMP, 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á — từ bài M02 đến M12. Đọ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         BIGSERIAL PRIMARY KEY,
  email      TEXT UNIQUE NOT NULL,
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

id BIGSERIAL PRIMARY KEY

BIGSERIAL là cú pháp rút gọn cho BIGINT cộng một sequence tự tăng. Vì sao không dùng SERIAL (4 byte, tối đa khoảng 2,1 tỷ)? Hệ thống với vài triệu user tạo mới mỗi ngày có thể chạm giới hạn đó sau vài năm — và khi sequence bị wraparound, mọi INSERT sau đó thất bại. BIGSERIAL (8 byte, tối đa hơn 9 triệu tỷ) loại trừ rủi ro này hoàn toàn. Vì sao không UUID? Module 4 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 TEXT UNIQUE NOT NULL

PostgreSQL TEXT không có giới hạn độ dài và không chậm hơn VARCHAR(n) — cả hai đều lưu dưới dạng varlena. Convention trong khoá này: dùng TEXT cho mọi chuỗi trừ khi cần ràng buộc độ dài cụ thể (ví dụ mã bưu chính 5 ký tự). 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 TIMESTAMPTZ NOT NULL DEFAULT now()

TIMESTAMPTZ (timestamp with time zone) lưu thời điểm theo UTC và tự động chuyển đổi sang timezone của session khi hiển thị. TIMESTAMP (không có TZ) lưu thời điểm nguyên xi không có context timezone — khi deploy hệ thống sang server ở múi giờ khác hoặc khi user ở nhiều timezone khác nhau truy vấn, giá trị hiển thị sai lệch 7-12 giờ mà không có lỗi rõ ràng. DEFAULT now() tự điền giá trị tại thời điểm INSERT — không cần app code truyền vào.

4. projects + project_members — M:N pattern

CREATE TABLE projects (
  id         BIGSERIAL PRIMARY KEY,
  owner_id   BIGINT NOT NULL REFERENCES users(id),
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Pattern giống users: BIGSERIAL PK, TEXT cho string, TIMESTAMPTZ 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 phải là BIGINT (không phải BIGSERIAL) vì BIGSERIAL chỉ dùng khi tạo column mới có sequence riêng; 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       TEXT NOT NULL CHECK (role IN ('owner', 'member', 'viewer')),
  joined_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  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.

BIGINT NOT NULL REFERENCES users(id)

FK type phải khớp với PK type của table tham chiếu. users.id là BIGSERIAL — nhưng BIGSERIAL khai báo tạo ra column kiểu BIGINT với sequence kèm theo. Khi tham chiếu từ table khác, dùng BIGINT — không phải BIGSERIAL.

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

Constraint inline kiểu enum cho 3 giá trị cố định. Alternative là CREATE TYPE role_enum AS ENUM ('owner', 'member', 'viewer') và dùng role role_enum NOT NULL. Cách CHECK đơn giản hơn cho số lượng value nhỏ và không cần tạo type riêng; ENUM type phù hợp hơn khi cùng tập giá trị đó được dùng ở nhiều table. Khoá này dùng CHECK 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          BIGSERIAL PRIMARY KEY,
  project_id  BIGINT NOT NULL REFERENCES projects(id),
  assignee_id BIGINT REFERENCES users(id),
  title       TEXT NOT NULL,
  status      TEXT NOT NULL CHECK (status IN ('todo', 'doing', 'done', 'archived')) DEFAULT 'todo',
  due_at      TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

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 TIMESTAMPTZ — 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 TIMESTAMPTZ NOT NULL DEFAULT now()

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 6 của khoá này sẽ thêm trigger đó. Đâ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         BIGSERIAL PRIMARY KEY,
  task_id    BIGINT NOT NULL REFERENCES tasks(id),
  user_id    BIGINT NOT NULL REFERENCES users(id),
  body       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

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, PostgreSQL sẽ block thao tác đó với lỗi FK violation thay vì tự xoá comment theo. Module 4 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 taskflow → Database Navigator → chọn schema public → 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. 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).
• BIGSERIAL không SERIAL: tránh wraparound ở giới hạn 2,1 tỷ row — dùng BIGSERIAL làm mặc định cho mọi PK mới.
• TIMESTAMPTZ không TIMESTAMP: lưu UTC, hiển thị theo session timezone — an toàn cho hệ thống multi-region và multi-timezone.
• TEXT không VARCHAR(n): PostgreSQL TEXT không có size limit và không chậm hơn VARCHAR(n) — dùng TEXT trừ khi cần ràng buộc độ dài cụ thể.
• 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 (M06), JSONB + vector (M09), RLS (M10), partition (M12) — không cần có sẵn từ ngày đầu.

11. Tự kiểm tra

CREATE TABLE customers (
id BIGSERIAL PRIMARY KEY,
email TEXT UNIQUE NOT NULL
);

CREATE TABLE plans (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL
);

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

Bài tiếp theo: SQL flavor map — Postgres khác MySQL ở đâu