Bài 51 — Cheat Sheet: SQL DDL Patterns — Data Modeling and ERD Mastery

Mở đầu — vì sao bài này quan trọng

Suốt cả khóa học, bạn đã học cách vẽ ERD, chuẩn hóa dữ liệu, chọn khóa chính, mô hình hóa quan hệ. Nhưng đến một lúc nào đó, bản vẽ đẹp trên Lucidchart hay dbdiagram phải "hạ cánh" xuống thành code SQL thật để lập trình viên hoặc DBA chạy trên MySQL, PostgreSQL, SQL Server. Đó chính là DDL — Data Definition Language — nhóm câu lệnh SQL dùng để định nghĩa cấu trúc của cơ sở dữ liệu: CREATE, ALTER, DROP.

Bạn có thể nghĩ: "Tôi là BA, viết DDL là việc của dev mà?" Đúng một phần. Nhưng trong thực tế Việt Nam, ranh giới này rất mờ. Rất nhiều dự án ở các công ty như FPT Software, VNG, MoMo, hoặc các startup nhỏ, người BA/Data Analyst phải tự viết bảng nháp DDL để chốt schema với team dev, hoặc phải đọc hiểu DDL người khác viết để review xem có đúng mô hình nghiệp vụ không. Một BA đọc được DDL sẽ phát hiện ngay chuyện "anh dev để cột số tiền là FLOAT" — một lỗi chết người mà nếu chỉ nhìn ERD thì không thấy.

Bài này là một cheat sheet — sổ tay tra cứu nhanh các mẫu (pattern) DDL bạn sẽ dùng đi dùng lại 80% thời gian. Mục tiêu không phải dạy bạn thành DBA, mà cho bạn một bộ khuôn mẫu chuẩn để copy, chỉnh sửa và áp dụng ngay, kèm hiểu biết đủ sâu để không viết sai. Đây là bài mang tính "công cụ" — hãy bookmark lại và mở ra mỗi khi cần dựng bảng mới.

Khái niệm cốt lõi

Mẫu bảng chuẩn (Standard Table Template)

Đây là khung xương mà gần như mọi bảng nghiệp vụ đều nên tuân theo. Tôi dùng cú pháp MySQL 8 / MariaDB làm ví dụ chính vì đó là DBMS phổ biến nhất ở thị trường Việt Nam:

CREATE TABLE customer (
  -- 1. Khóa chính (Primary Key) — luôn đứng đầu
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  -- 2. Các thuộc tính nghiệp vụ (Business attributes)
  full_name    VARCHAR(150)  NOT NULL,
  email        VARCHAR(255)  NOT NULL,
  phone        VARCHAR(20),
  status       VARCHAR(20)   NOT NULL DEFAULT 'active',
  -- 3. Khóa ngoại (Foreign Keys)
  province_id  INT UNSIGNED,
  -- 4. Cột audit (Audit columns) — luôn đứng cuối
  created_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP,
  deleted_at   TIMESTAMP     NULL,  -- 5. Ràng buộc (Constraints) khai báo tường minh
  CONSTRAINT uq_customer_email UNIQUE (email),
  CONSTRAINT fk_customer_province
    FOREIGN KEY (province_id) REFERENCES province(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Hãy để ý thứ tự nhất quán: PK → thuộc tính nghiệp vụ → FK → cột audit → constraints. Sự nhất quán này giúp bất kỳ ai mở bảng ra cũng đọc được ngay, giống như mọi cuốn sách đều có mục lục ở đầu và phụ lục ở cuối.

Một chi tiết cực kỳ quan trọng với dữ liệu tiếng Việt: CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci. Nếu bạn để utf8 (thực chất là utf8mb3 cũ) thì các ký tự có dấu và emoji có thể bị lỗi. utf8mb4_unicode_ci giúp so sánh chuỗi tiếng Việt đúng hơn.

Chọn kiểu dữ liệu (Data Types) đúng

Đây là nơi BA hay để lộ điểm yếu. Ghi nhớ các mẫu vàng:

Định danh/ID: BIGINT UNSIGNED AUTO_INCREMENT. Dùng BIGINT thay vì INT cho các bảng lớn (giao dịch, log) vì INT UNSIGNED chỉ đến ~4,29 tỷ — MoMo hay Shopee một năm đã vượt.
Tiền tệ: DECIMAL(19,4) — TUYỆT ĐỐI KHÔNG dùng FLOAT/DOUBLE. Số thực dấu phẩy động làm tròn sai, cộng dồn triệu giao dịch sẽ lệch tiền.
Chuỗi độ dài giới hạn: VARCHAR(n). Tên VARCHAR(150), email VARCHAR(255), số điện thoại VN VARCHAR(20) (giữ chuỗi, không dùng số vì có số 0 đầu).
Văn bản dài: TEXT (mô tả, ghi chú).
Trạng thái/enum: VARCHAR(20) + CHECK thay vì kiểu ENUM gốc — vì thêm giá trị mới vào ENUM phải ALTER bảng, rất phiền.
Ngày giờ: DATE cho ngày sinh, DATETIME/TIMESTAMP cho thời điểm sự kiện, BOOLEAN (thực chất TINYINT(1)) cho cờ đúng/sai.
Dữ liệu JSON: JSON (MySQL 5.7+, PostgreSQL dùng JSONB).

Ràng buộc (Constraints) — hàng rào bảo vệ dữ liệu

Constraints là cách bạn ép business rule vào ngay tầng CSDL, để dù ứng dụng có bug thì dữ liệu bẩn vẫn không lọt vào:

-- NOT NULL: bắt buộc phải có giá trị
email VARCHAR(255) NOT NULL,
-- UNIQUE: không trùng
CONSTRAINT uq_customer_email UNIQUE (email),
-- CHECK: kiểm tra điều kiện (MySQL 8.0.16+ / PostgreSQL)
CONSTRAINT chk_status CHECK (status IN ('active','inactive','banned')),
CONSTRAINT chk_age    CHECK (age >= 18),
-- DEFAULT: giá trị mặc định
status VARCHAR(20) NOT NULL DEFAULT 'active',-- FOREIGN KEY: toàn vẹn tham chiếu
CONSTRAINT fk_order_customer
  FOREIGN KEY (customer_id) REFERENCES customer(id)
  ON DELETE RESTRICT ON UPDATE CASCADE

Về ON DELETE: RESTRICT (chặn xóa nếu còn con — an toàn nhất, mặc định nên dùng), CASCADE (xóa cha thì xóa luôn con — cẩn thận!), SET NULL (đặt NULL khi cha bị xóa).

Quy ước đặt tên (Naming Conventions)

Một cheat sheet không có quy ước tên là vô dụng. Mẫu tôi khuyên dùng:

Bảng: số ít, snake_case: customer, order_item (một số team thích số nhiều customers — miễn nhất quán toàn dự án).
Cột: snake_case: full_name, created_at.
Khóa ngoại: <bảng_tham_chiếu>_id: customer_id, province_id.
Tên constraint: có tiền tố loại — pk_, fk_, uq_, chk_, idx_. Ví dụ fk_order_customer, idx_order_created_at. Đặt tên tường minh giúp khi lỗi bung ra, log báo đúng tên constraint, bạn biết ngay vấn đề nằm đâu.

Bảng nối cho quan hệ Many-to-Many

Mẫu quen thuộc bạn sẽ gõ hàng trăm lần — bảng nối (junction/associative table):

CREATE TABLE student_course (
  student_id  BIGINT UNSIGNED NOT NULL,
  course_id   BIGINT UNSIGNED NOT NULL,
  enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (student_id, course_id),        -- composite PK
  CONSTRAINT fk_sc_student FOREIGN KEY (student_id) REFERENCES student(id),
  CONSTRAINT fk_sc_course  FOREIGN KEY (course_id)  REFERENCES course(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Khóa chính tổ hợp (student_id, course_id) vừa đảm bảo một sinh viên không đăng ký trùng một khóa, vừa là index truy vấn nhanh.

Tình huống thực tế

Tình huống 1 — Sàn TMĐT "Chợ Sen" và cột tiền tai hại

Một startup TMĐT giả định tên Chợ Sen ở TP.HCM thuê một dev junior dựng nhanh MVP. Cậu ấy tạo bảng orders với cột total_amount FLOAT. Ba tháng đầu chạy êm. Đến khi đối soát với ví điện tử, kế toán phát hiện tổng doanh thu trên hệ thống lệch hơn 3,7 triệu đồng so với sao kê ngân hàng, dù không có giao dịch nào bị mất.

Nguyên nhân: FLOAT lưu số thập phân theo nhị phân, 199000.10 thực chất được lưu xấp xỉ 199000.0999.... Cộng dồn hàng chục nghìn đơn, sai số tích lũy thành con số thấy được. Khi BA của công ty review lại DDL, chỉ cần đổi sang DECIMAL(19,4) và migrate dữ liệu là hết.

Bài học: Với dữ liệu tiền tệ, DECIMAL là bắt buộc, không có ngoại lệ. Một BA đọc DDL và bắt được lỗi này đã cứu công ty khỏi rắc rối kế toán và mất niềm tin.

Tình huống 2 — Ngân hàng số và ràng buộc trạng thái tài khoản

Một dự án ngân hàng số tại một ngân hàng VN (tương tự TPBank hay Timo) có bảng account với cột status. Ban đầu team không đặt CHECK constraint, để ứng dụng tự quản. Kết quả: qua nhiều đợt release, dữ liệu sản xuất xuất hiện các giá trị lộn xộn 'ACTIVE', 'active', 'Active', 'A', thậm chí 'hoat_dong' do một API cũ ghi vào. Báo cáo đếm số tài khoản hoạt động sai bét vì mỗi biến thể bị đếm riêng.

Đội Data quyết định thêm hàng rào:

ALTER TABLE account
  ADD CONSTRAINT chk_account_status
  CHECK (status IN ('active','frozen','closed'));

Trước khi thêm được constraint, họ phải chạy một đợt làm sạch dữ liệu (data cleansing) để chuẩn hóa toàn bộ giá trị cũ về đúng ba trạng thái. Từ đó, mọi bản ghi mới có giá trị lạ sẽ bị CSDL từ chối ngay lập tức.

Bài học: CHECK constraint là "người gác cổng" ép business rule tại tầng dữ liệu. Đừng tin tưởng hoàn toàn vào tầng ứng dụng, vì ứng dụng có nhiều phiên bản, nhiều API, nhiều lập trình viên — nhưng CSDL chỉ có một.

Tình huống 3 — LMS và bài toán soft delete

Một nền tảng học trực tuyến (LMS) giả định phục vụ 50.000 học viên gặp yêu cầu từ bộ phận pháp lý: "Khi học viên yêu cầu xóa tài khoản, không được xóa cứng ngay vì còn liên quan chứng chỉ đã cấp và nghĩa vụ lưu trữ theo quy định." Nếu dùng DELETE thật, các bảng certificate, payment tham chiếu tới sẽ vỡ khóa ngoại.

Giải pháp là mẫu soft delete dùng cột deleted_at:

-- Thay vì DELETE, ta cập nhật:
UPDATE student SET deleted_at = CURRENT_TIMESTAMP WHERE id = 12345;-- Và mọi truy vấn nghiệp vụ luôn lọc:
SELECT * FROM student WHERE deleted_at IS NULL;

Cột deleted_at TIMESTAMP NULL trong mẫu bảng chuẩn chính là để phục vụ tình huống này. Bản ghi vẫn còn trong bảng, khóa ngoại không vỡ, nhưng về mặt nghiệp vụ coi như đã xóa.

Bài học: Cột audit created_at, updated_at, deleted_at không thừa. Chúng giải quyết các yêu cầu pháp lý và truy vết mà bạn sẽ gặp ở gần như mọi hệ thống nghiêm túc tại Việt Nam, đặc biệt trong bối cảnh Luật Bảo vệ dữ liệu cá nhân (PDPL).

Hướng dẫn từng bước

Khi cần dựng một bảng mới từ ERD, hãy đi theo quy trình 7 bước sau:

Xác định tên bảng theo quy ước (số ít, snake_case). Ví dụ từ entity "Đơn hàng" → order.

Khai báo khóa chính đầu tiên. Mặc định dùng surrogate key: id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT.

Liệt kê thuộc tính nghiệp vụ từ Data Dictionary, chọn kiểu dữ liệu đúng cho từng cột. Dừng lại tự hỏi ở mỗi cột: đây là tiền (DECIMAL)? ngày (DATE/DATETIME)? chuỗi có dấu (utf8mb4)? Đánh dấu NOT NULL cho các cột bắt buộc theo nghiệp vụ.

Thêm khóa ngoại ứng với các quan hệ trong ERD, đặt tên theo mẫu <bảng>_id.

Thêm ba cột audit ở cuối: created_at, updated_at, deleted_at (nếu cần soft delete).

Khai báo constraints tường minh ở cuối bảng: UNIQUE, CHECK, FOREIGN KEY, mỗi cái có tên rõ ràng với tiền tố.

Thêm phần đuôi engine/charset: ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci. Dùng InnoDB vì nó hỗ trợ khóa ngoại và giao dịch (MyISAM thì không).

Sau khi có CREATE TABLE, các thao tác sửa đổi về sau dùng ALTER TABLE:

-- Thêm cột mới ALTER TABLE customer ADD COLUMN date_of_birth DATE NULL; -- Thêm index để truy vấn nhanh ALTER TABLE order ADD INDEX idx_order_created_at (created_at); -- Đổi kiểu dữ liệu một cột ALTER TABLE order MODIFY COLUMN total_amount DECIMAL(19,4) NOT NULL;

-- Xóa cột (cẩn thận, không hoàn tác được dữ liệu!) ALTER TABLE customer DROP COLUMN old_field;

Và DROP TABLE để xóa cả bảng — dùng có chủ đích, thường kèm IF EXISTS trong script:

DROP TABLE IF EXISTS temp_import;

Lỗi thường gặp & mẹo

Lỗi 1 — Dùng FLOAT cho tiền. Đã nói ở trên nhưng nhắc lại vì đây là lỗi phổ biến nhất. Luôn DECIMAL(19,4).

Lỗi 2 — Quên UNSIGNED và dùng INT cho ID. INT chỉ đến ~2,1 tỷ (có dấu) hoặc ~4,29 tỷ (UNSIGNED). Bảng log, giao dịch nên BIGINT UNSIGNED ngay từ đầu để khỏi phải migrate đau đớn về sau.

Lỗi 3 — Charset sai làm hỏng tiếng Việt. Để utf8 cũ hoặc latin1 khiến "Nguyễn" thành "Nguy?n". Luôn utf8mb4_unicode_ci.

Lỗi 4 — Không đặt tên constraint. Để CSDL tự sinh tên như customer_ibfk_1 thì khi lỗi bung ra bạn không biết constraint nào. Luôn đặt tên tường minh fk_..., uq_....

Lỗi 5 — Số điện thoại lưu kiểu số. Số điện thoại VN có số 0 đầu (0912...), lưu INT sẽ mất số 0. Luôn VARCHAR.

Lỗi 6 — Lạm dụng ON DELETE CASCADE. Nghe tiện nhưng nguy hiểm: xóa một khách hàng có thể quét sạch toàn bộ lịch sử đơn hàng của họ. Mặc định nên RESTRICT, chỉ dùng CASCADE khi con thật sự vô nghĩa khi thiếu cha (ví dụ dòng chi tiết đơn hàng so với đơn hàng).

Mẹo 1 — Luôn viết DDL vào file .sql có version control (Git). Đừng gõ trực tiếp lên production. Mỗi thay đổi schema là một migration file có thứ tự.

Mẹo 2 — Test DDL trên môi trường staging trước. Một lệnh ALTER TABLE trên bảng lớn có thể khóa bảng nhiều phút, làm treo ứng dụng thật.

Mẹo 3 — Dùng IF NOT EXISTS / IF EXISTS trong script tự động để chạy lại không lỗi: CREATE TABLE IF NOT EXISTS ....

Mẹo 4 — Với PostgreSQL, nhớ khác biệt cú pháp: SERIAL/BIGSERIAL hoặc GENERATED ALWAYS AS IDENTITY thay cho AUTO_INCREMENT; TIMESTAMPTZ cho thời gian có múi giờ; JSONB thay JSON; và dùng BOOLEAN thật thay vì TINYINT(1).

Bài tập thực hành

Hãy áp dụng cheat sheet để tự tay viết DDL cho tình huống sau:

Đề bài: Một tiệm cà phê chuỗi tại Đà Nẵng cần hệ thống quản lý đơn hàng đơn giản với ba entity:

product — sản phẩm: tên (bắt buộc), giá bán, trạng thái (available/sold_out), có audit.
order — đơn hàng: mã bàn, tổng tiền, phương thức thanh toán (cash/card/momo/zalopay), có audit.
order_item — chi tiết đơn: nối order và product, kèm số lượng và đơn giá tại thời điểm bán.

Yêu cầu bạn thực hiện:

Viết đầy đủ ba câu CREATE TABLE theo mẫu bảng chuẩn.
Cột tiền phải dùng DECIMAL(19,4).
Trạng thái và phương thức thanh toán phải có CHECK constraint.
order_item phải có khóa ngoại tới cả order (với ON DELETE CASCADE, vì chi tiết vô nghĩa khi đơn bị hủy) và product (với ON DELETE RESTRICT, không cho xóa sản phẩm còn nằm trong đơn).
Đặt tên tất cả constraint theo quy ước tiền tố.
Thêm một index trên order.created_at để truy vấn báo cáo doanh thu theo ngày.

Câu hỏi tư duy: Ở order_item, tại sao ta phải lưu unit_price riêng thay vì mỗi lần cần thì JOIN sang product lấy giá? (Gợi ý: điều gì xảy ra với báo cáo cũ khi tiệm tăng giá cà phê?)

Sau khi viết xong, hãy tự kiểm bằng checklist: Đã có PK BIGINT UNSIGNED chưa? Có ba cột audit chưa? Charset utf8mb4 chưa? Mọi FK và CHECK đã có tên chưa? Nếu trả lời "rồi" cho tất cả, bạn đã có một schema production-ready.

Tóm tắt

DDL là cầu nối biến mô hình dữ liệu trên giấy thành cấu trúc thật trong CSDL. Là một BA về dữ liệu, bạn không cần thành DBA, nhưng cần đọc, hiểu và viết được các mẫu DDL phổ biến để chốt schema với dev và bắt lỗi từ sớm.

Những điều cần khắc cốt ghi tâm từ cheat sheet này:

Mẫu bảng chuẩn có thứ tự nhất quán: PK → thuộc tính → FK → audit → constraints.
Kiểu dữ liệu vàng: BIGINT UNSIGNED cho ID, DECIMAL(19,4) cho tiền (không bao giờ FLOAT), VARCHAR cho số điện thoại, utf8mb4_unicode_ci cho tiếng Việt.
Constraints là hàng rào: NOT NULL, UNIQUE, CHECK, FOREIGN KEY ép business rule ngay tại tầng dữ liệu.
Cột audit created_at/updated_at/deleted_at phục vụ truy vết, soft delete và tuân thủ pháp lý.
Quy ước đặt tên rõ ràng với tiền tố pk_, fk_, uq_, chk_, idx_ giúp bảo trì dễ dàng.

Hãy bookmark bài này. Mỗi lần dựng bảng mới, mở ra, copy khung mẫu, và điều chỉnh. Sau vài chục lần, các pattern này sẽ nằm trong đầu bạn như bảng cửu chương. Ở bài tiếp theo, chúng ta sẽ nhìn xa hơn code để bàn về lộ trình sự nghiệp của một Data Architect.