Đến nội dung chính
notes.vinhnq.com

Tìm kiếm

Database & ORMSenior24 phút đọc

Quy trình data modeling: access pattern, loại bảng, khoá ngoại và table template

Ôn 16 thẻ
Trong bài này

Cụm bài Data modeling: 1. Quy trình data modeling · 2. Cột JSON, DDL online, kiểu dữ liệu và view · 3. Case study đa ngôn ngữ, sắp xếp, lịch và báo cáo

Schema là thứ khó sửa nhất của backend: code sai thì deploy lại, schema sai thì phải migrate hàng trăm triệu dòng khi hệ thống vẫn chạy. Bài này là nửa đầu quy trình: bắt đầu từ truy vấn, phân loại bảng, chuẩn hoá hay không, giữ hay bỏ khoá ngoại, đặt cột tham chiếu và table template. Phần 2 nói về cột JSON, đổi schema không downtime, kiểu dữ liệu và view; phần 3 áp dụng tất cả vào sáu bài toán cụ thể.

Nên đọc trước: chuẩn hoá 1NF–5NF cho lý thuyết dạng chuẩn, index và EXPLAIN cho composite index. Số đo chạy thật trên PostgreSQL 16 và MySQL 8.4 (Docker, laptop Apple Silicon), chỉ để so tỉ lệ, không phải benchmark.

1. Quy trình: bắt đầu từ access pattern, không phải từ bảng

Ý chính: liệt kê truy vấn và lệnh ghi chính (kèm tần suất, độ trễ mong muốn) trước, rồi mới gom trường thành bảng. Bảng vẽ trước truy vấn trông hợp lý nhưng chưa chắc trả lời nhanh câu hỏi quan trọng nhất.

Access pattern là cách dữ liệu được đọc và ghi: truy vấn nào, lọc gì, sắp gì, bao nhiêu lần mỗi giây. Ba giai đoạn, theo đúng thứ tự:

  1. Phân tích: yêu cầu, danh sách access pattern, schema hiện có (nếu sửa hệ thống cũ), danh sách trường sơ bộ.
  2. Mô hình hoá: gom trường thành bảng, tinh chỉnh, chọn khoá và quan hệ.
  3. Toàn vẹn dữ liệu: review, chuẩn hoá đến đâu, phi chuẩn hoá chỗ nào.

Sai lầm phổ biến: nhảy thẳng vào bước 2, mở công cụ vẽ ERD kéo ra mười cái hộp.

Ví dụ: nền tảng blog nhiều tác giả

# Truy vấn Tần suất Mục tiêu
Q1 Feed: 20 bài mới nhất của tác giả thuộc một quốc gia 300 req/s p95 < 50 ms
Q2 Chi tiết bài viết kèm số bình luận 1.000 req/s p95 < 20 ms
Q3 Dashboard tác giả: các bài của tôi, mới nhất trước 20 req/s p95 < 200 ms
W1 Đăng bài 5 req/s p95 < 300 ms
W2 Thêm bình luận 50 req/s p95 < 100 ms

Trường sơ bộ tự gom thành nhóm (tác giả, bài viết, bình luận), thành bảng. Bảng truy vấn còn lộ hai điều ERD không thấy:

  • Q1 lọc theo tác giả nhưng sắp theo bài viết: dấu hiệu cần phi chuẩn hoá (số đo ở mục 3).
  • Q2 đọc gấp 20 lần W2 ghi: đáng có một cột đếm bình luận.

2. Entity, ERD và bốn loại bảng

Ý chính: entity là thứ có danh tính và vòng đời; mọi bảng còn lại là relation, support hoặc config. Biết bảng thuộc loại nào thì biết nó cần cột gì.

Entity có danh tính (id không đổi suốt đời) và vòng đời (thường là cột status). Bài viết là entity: được tạo, đăng, ẩn, xoá. Quốc gia trong danh mục không có vòng đời đáng kể; nó là dữ liệu cấu hình.

Coi status là máy trạng thái (tập trạng thái cố định, bước chuyển được phép), không phải chuỗi tự do:

stateDiagram-v2
  [*] --> DRAFT
  DRAFT --> PUBLISHED: publish
  PUBLISHED --> HIDDEN: moderator hides
  HIDDEN --> PUBLISHED: restore
  DRAFT --> DELETED: delete
  PUBLISHED --> DELETED: delete
  DELETED --> [*]
  • Database giữ tập giá trị hợp lệ: CHECK (status IN (...)) hoặc enum, xem enum trong database và API.
  • Bước chuyển hợp lệ thường kiểm ở tầng domain, kèm điều kiện UPDATE ... WHERE status = 'DRAFT' để hai request đồng thời không cùng chuyển.

ERD (sơ đồ entity và quan hệ) trên slide dùng ký pháp Chen (hộp, hình thoi, oval; sinh viên N–M lớp học, lớp N–1 giáo viên). Ký pháp crow’s foot (Mermaid erDiagram) gọn và gần bảng hơn.

Loại bảng Vai trò Ví dụ Đặc điểm
Entity Đối tượng nghiệp vụ có vòng đời author, post, booking Có status, version, cột audit
Relation Nối quan hệ N–M post_tag (post_id, tag_id) Khoá chính ghép, thường không có id riêng
Support Dữ liệu phụ của một entity, tách ra vì 1–N hoặc vì lớn post_revision, author_address Có khoá ngoại về entity cha
Config Danh mục ít đổi country, currency, system_config Đọc nhiều, cache được, thường khoá tự nhiên

Quy ước: entity dùng template đầy đủ (mục 5), relation chỉ cần created_at, config dùng khoá tự nhiên (country_code char(2)) và cache ở app.

3. Chuẩn hoá hay phi chuẩn hoá: một khung quyết định

Ý chính: chuẩn hoá làm nền, rồi phi chuẩn hoá có chủ đích ở chỗ đo được, điển hình là khi truy vấn nóng lọc ở một bảng nhưng sắp ở bảng khác. Cái giá là phải tự giữ dữ liệu dư thừa nhất quán.

Lý thuyết dạng chuẩn ở bài chuẩn hoá. Hai mặt cần nhớ:

  • Chuẩn hoá (tách bảng lớn thành bảng nhỏ có quan hệ): ít trùng lặp, toàn vẹn tốt; nhưng phải JOIN, và có truy vấn không index nào phục vụ trọn.
  • Phi chuẩn hoá (gộp bảng, chép dữ liệu dư thừa): đọc nhanh, index được tổ hợp cột của hai bảng; nhưng tốn chỗ, tự giữ nhất quán.

Với Q1 (100.000 tác giả, 2 triệu bài, SG chỉ 0,1% tác giả), cùng một câu SQL:

Cách Quốc gia Buffer đọc Thời gian
JOIN post–author, lọc ở author VN (60% tác giả) đọc 32 bài 0,2 ms
JOIN post–author, lọc ở author SG (0,1%) 125.000 (≈41.000 bài) 63 ms
Cột post.author_country + index (author_country, created_at) SG 23 0,074 ms

Cái giá: tác giả đổi quốc gia thì phải cập nhật mọi bài của họ.

Câu SQL và kế hoạch thực thi của hai cách
-- Chuẩn hoá: lọc ở author, sắp xếp ở post
SELECT p.post_id, p.title
FROM post p JOIN author a USING (author_id)
WHERE a.country = 'SG'
ORDER BY p.created_at DESC LIMIT 20;

PostgreSQL đi ngược index post (created_at) và kiểm từng tác giả. Với VN chỉ cần đọc 32 bài là đủ 20 kết quả. Với SG phải đọc khoảng 41.000 bài và 125.000 buffer mới gom đủ 20 dòng. Độ trễ phụ thuộc vào giá trị tham số.

SELECT post_id, title FROM post
WHERE author_country = 'SG'
ORDER BY created_at DESC LIMIT 20;
-- Index Scan Backward using post_country_created_idx
-- Buffers: 23, Execution Time: 0.074 ms

Giữ cột đếm nhất quán

Ý chính: cột đếm (comment_count) cập nhật nguyên tử trong SQL, qua cùng transaction, trigger hoặc event; không đọc lên app cộng một rồi ghi lại.

Cách Nhất quán Chi phí
Cùng transaction: SET comment_count = comment_count + 1 Mạnh Mọi chỗ ghi phải nhớ; dòng hot bị tranh khoá
Trigger trong database Mạnh Logic ẩn, khó debug (bài tập 2)
Event bất đồng bộ (outbox, Kafka) Cuối cùng Có độ trễ; cần idempotency và đối soát

4. Khoá ngoại: giữ hay bỏ

Ý chính: mặc định giữ khoá ngoại khi các bảng cùng database và service. Chi phí thật không phải lần kiểm tra mà là khoá trên dòng cha, nặng hơn hẳn ở InnoDB.

Slide nêu quan điểm cá nhân: bỏ ràng buộc khoá ngoại, kiểm tra ở tầng ứng dụng. Quan điểm này phổ biến ở công ty dùng MySQL sharding, nhưng không nên áp mặc định: ba trong bốn lý do của slide không còn đúng.

Bốn lý do bỏ FK của slide và phản biện
Lý do của slide Thực tế
Database phải kiểm khoá cha mỗi lần ghi bảng con Chỉ là một lần tra index khoá chính bảng cha, thường nằm sẵn trong RAM
Ứng dụng phải phân tích exception để biết lý do lỗi Spring đã dịch thành DataIntegrityViolationException có tên constraint
Backup/restore phải theo thứ tự pg_dump, mysqldump đã lo
Schema phức tạp hơn Đúng, nhưng đổi lại không bao giờ có dòng mồ côi

Slide cũng nhắc một ý đúng: khoá ngoại (constraint) khác cột tham chiếu; bỏ constraint thì cột customer_id vẫn còn và vẫn cần index.

Khoá trên dòng cha: PostgreSQL và InnoDB khác nhau

Ý chính: khi có dòng con mới chưa commit, PostgreSQL chỉ chặn xoá hoặc đổi khoá của dòng cha; InnoDB chặn mọi UPDATE vào dòng cha.

Phiên A insert đơn hàng của khách 1, chưa commit; phiên B thao tác trên dòng khách 1 (lock_timeout = 1s):

Thao tác của phiên B PostgreSQL 16 MySQL 8.4 (InnoDB)
UPDATE customer SET name = ... (không đổi khoá) Chạy ngay (0,7 ms) Bị chặn, hết 1 giây thì lỗi 1205
DELETE FROM customer WHERE id = 1 Bị chặn Bị chặn
UPDATE customer SET id = 10 Bị chặn Bị chặn

Kiểm tra ở ứng dụng có race condition

Ý chính: SELECT xem cha có không rồi INSERT để lọt dòng mồ côi khi cha bị xoá ở giữa. Muốn đúng phải khoá dòng cha (SELECT ... FOR SHARE), tức tự viết lại thứ FK làm sẵn.

Dòng mồ côi là dòng con trỏ tới một dòng cha không còn tồn tại.

Thí nghiệm race condition: có FK và không có FK

Trên PostgreSQL, bảng đơn hàng không có FK:

  1. Phiên A BEGIN, thấy khách 2 tồn tại, ngủ 2 giây.
  2. Trong lúc đó phiên B xoá khách 2 và commit.
  3. Phiên A insert đơn hàng cho khách 2 và commit thành công: một đơn hàng mồ côi.

Làm lại với bảng có FK: phiên B xoá được (A chưa insert), nhưng lệnh insert của A bị từ chối với violates foreign key constraint.

Cột tham chiếu đặt ở bảng nào

Ý chính: với quan hệ 1–1, entity sinh ra sau giữ tham chiếu tới entity sinh ra trước, kèm UNIQUE để giữ 1–1.

Bài toán trên slide: đặt hàng tạo một order rồi một giao dịch thanh toán; quyên góp chỉ tạo giao dịch, không có order. Order và giao dịch quan hệ 1–1. Câu trả lời: đặt order_id trong bảng giao dịch.

CREATE TABLE orders (
  order_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL,
  total      bigint NOT NULL,
  status     text   NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE payment_txn (
  txn_id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  purpose     text   NOT NULL CHECK (purpose IN ('ORDER', 'DONATION')),
  order_id    bigint REFERENCES orders,          -- NULL với quyên góp
  campaign_id bigint,                             -- chỉ có với quyên góp
  amount      bigint NOT NULL CHECK (amount > 0),
  created_at  timestamptz NOT NULL DEFAULT now(),
  CHECK ((purpose = 'ORDER') = (order_id IS NOT NULL)),
  UNIQUE (order_id)                               -- giữ quan hệ 1–1
);
  • Khi tạo order, giao dịch chưa có; đặt txn_id trong orders buộc insert cột rỗng rồi update lại.
  • Thêm loại giao dịch mới (nạp ví, hoàn tiền) không đụng tới orders: đó là ý “tốt cho khả năng mở rộng” của slide.
  • Bỏ UNIQUE (order_id) là thành 1–N (thanh toán nhiều lần) mà không đổi cột.

5. Table template: cột nghiệp vụ và cột kỹ thuật

Ý chính: entity table có cột nghiệp vụ (id, cột riêng, status, data JSON) và cột kỹ thuật (version, created_at/by, updated_at/by). Mỗi cột kỹ thuật làm một việc; bảng không cần việc đó thì bỏ cột đó.

CREATE TABLE app_user (
  user_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  -- nghiệp vụ
  name       varchar(100) NOT NULL,
  birth_date date,
  status     text NOT NULL DEFAULT 'ACTIVE'
             CHECK (status IN ('ACTIVE', 'LOCKED', 'CLOSED')),
  data       jsonb NOT NULL DEFAULT '{}',
  -- kỹ thuật
  version    int NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by text NOT NULL
);
Cột Để làm gì
version Khoá lạc quan: WHERE id = ? AND version = ?, 0 dòng nghĩa là có người sửa trước
created_at Sắp xếp, phân trang keyset, partition theo thời gian
created_by, updated_by Ai (người dùng, admin, service, job) đã tạo, sửa
updated_at Đồng bộ tăng dần sang hệ khác (CDC, search index)
  • Slide dùng age SMALLINT; lưu birth_date đúng hơn vì tuổi đổi mỗi năm.
  • MySQL có ON UPDATE CURRENT_TIMESTAMP(3) cho updated_at; PostgreSQL cần trigger BEFORE UPDATE (bài tập 1) hoặc ORM.
  • Bảng chỉ thêm (sổ cái, log sự kiện) không cần updated_at, version; bảng relation chỉ cần created_at.
  • Cột data JSON có giới hạn riêng (phần 2). Soft delete (deleted_at) là quyết định riêng, có giá với unique index và mọi truy vấn (soft delete).
Code Spring Boot 3: AuditableEntity và AuditorAware

JPA và Spring Data lo phần lớn cột kỹ thuật:

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public abstract class AuditableEntity {

    @Version
    private int version;

    @CreatedDate
    @Column(nullable = false, updatable = false)
    private Instant createdAt;

    @CreatedBy
    @Column(nullable = false, updatable = false)
    private String createdBy;

    @LastModifiedDate
    @Column(nullable = false)
    private Instant updatedAt;

    @LastModifiedBy
    @Column(nullable = false)
    private String updatedBy;
}

@Configuration
@EnableJpaAuditing
class AuditConfig {
    @Bean
    AuditorAware<String> auditorAware() {
        // người dùng đăng nhập, hoặc tên service/job khi chạy nền
        return () -> Optional.ofNullable(SecurityContextHolder.getContext().getAuthentication())
                .map(Authentication::getName)
                .or(() -> Optional.of("system"));
    }
}

6. Ngoài quan hệ: mô hình NoSQL cũng bắt đầu từ access pattern

Ý chính: với key-value hay document store, truy vấn không thiết kế trước thì gần như không trả lời được; bảng access pattern ở mục 1 chính là schema. (Phần này không có trong slide, mình thêm để đối chiếu.)

Database quan hệ cho chuẩn hoá trước, thêm index sau, vì SQL trả lời được truy vấn bất kỳ; DynamoDB, Cassandra, MongoDB thì không.

Cassandra, DynamoDB và MongoDB mô hình hoá theo truy vấn thế nào
  • Cassandra, DynamoDB: mỗi truy vấn chính thường có một bảng (hoặc index) với partition key và sort key chọn đúng cho nó; dữ liệu được nhân bản giữa các bảng. “Single-table design” của DynamoDB đặt nhiều loại entity vào một bảng, phân biệt bằng tiền tố khoá (AUTHOR#42, POST#2026-11-18#981) để một lần đọc lấy được cả cụm.
  • MongoDB: nhúng (embed) dữ liệu luôn đọc cùng nhau và có giới hạn số lượng (địa chỉ của user); tham chiếu (reference) dữ liệu tăng không giới hạn (bình luận của bài viral).

Bài tập

Bài 1: Áp table template vào schema đặt vé máy bay

Đề (diễn đạt lại): bài tập về nhà của slide là thiết kế schema đặt vé máy bay theo table template. Schema đầy đủ đã có ở bài thiết kế database đặt vé và bài booking, sổ cái, nên ở đây làm phần còn thiếu: áp template cho ba bảng flight, booking, entry và giải thích cột kỹ thuật nào hợp, cột nào không.

Lời giải bài 1: template cho flight, booking, entry

Chạy trên PostgreSQL 16:

CREATE FUNCTION touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END $$;

CREATE FUNCTION forbid_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION '% is append-only, % is not allowed', TG_TABLE_NAME, TG_OP;
END $$;

-- booking: aggregate root, có data jsonb cho thuộc tính phụ theo kênh bán
CREATE TABLE booking (
  booking_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  booking_code char(6)     NOT NULL UNIQUE,
  user_id      bigint      NOT NULL,
  status       text        NOT NULL DEFAULT 'PENDING_PAYMENT',
  total_amount bigint      NOT NULL CHECK (total_amount >= 0),
  currency     char(3)     NOT NULL DEFAULT 'VND',
  data         jsonb       NOT NULL DEFAULT '{}',
  version      int         NOT NULL DEFAULT 0,
  created_at   timestamptz NOT NULL DEFAULT now(),
  created_by   text        NOT NULL,
  updated_at   timestamptz NOT NULL DEFAULT now(),
  updated_by   text        NOT NULL
);
CREATE TRIGGER booking_touch BEFORE UPDATE ON booking
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

-- entry: bút toán sổ cái, chỉ thêm
CREATE TABLE entry (
  entry_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  txn_id     bigint      NOT NULL,
  account_id bigint      NOT NULL,
  amount     bigint      NOT NULL CHECK (amount <> 0),
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by text        NOT NULL
);
CREATE TRIGGER entry_append_only BEFORE UPDATE OR DELETE ON entry
  FOR EACH ROW EXECUTE FUNCTION forbid_change();

Kiểm tra:

  • Tạo booking với version = 0. Service thanh toán UPDATE ... SET status = 'PAID', version = version + 1 ... WHERE booking_id = 1 AND version = 0 trả 1 dòng.
  • Một request cũ cũng điều kiện version = 0 trả 0 dòng (bị phát hiện sửa chồng); updated_at đã lớn hơn created_at nhờ trigger.
  • UPDATE entry bị từ chối với entry is append-only, UPDATE is not allowed.

Từng quyết định:

  • flight (DDL giống bảng gốc, thêm version, created_*, updated_* và trigger touch_updated_at, không in lại): đủ bộ cột kỹ thuật vì giờ bay bị nhân viên vận hành và job đồng bộ lịch bay sửa; updated_by trả lời câu “ai đổi giờ chuyến VN213”. Không có cột data: thuộc tính chuyến bay ổn định và đều cần lọc.
  • booking: đủ bộ, data jsonb hợp cho thông tin theo kênh bán (mã đại lý, UTM, thiết bị) vốn thưa và không lọc. version bắt buộc vì booking bị nhiều bên sửa (khách, service thanh toán, job hết hạn). created_by có vẻ trùng user_id nhưng không: booking có thể do nhân viên tổng đài tạo hộ.
  • entry: chỉ created_at, created_by. Sổ cái không bao giờ sửa: sai thì ghi bút toán đảo. updated_* hay version ngầm cho phép sửa nên bỏ; trigger chặn UPDATE, DELETE biến quy ước thành ràng buộc. Ở production nên thêm REVOKE UPDATE, DELETE cho role của ứng dụng.

Bài 2: Cột đếm bình luận bằng trigger

Đề: giữ article.comment_count luôn bằng số bình luận thật, kể cả khi nhiều request ghi đồng thời và có người insert bằng SQL tay.

Lời giải bài 2: trigger đếm bình luận và lỗi search_path

Chạy trên PostgreSQL 16:

CREATE TABLE article (
  article_id    bigint PRIMARY KEY,
  title         text NOT NULL,
  comment_count int  NOT NULL DEFAULT 0 CHECK (comment_count >= 0)
);
CREATE TABLE comment (
  comment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  article_id bigint NOT NULL REFERENCES article,
  body       text   NOT NULL
);
CREATE INDEX ON comment (article_id);

CREATE FUNCTION sync_comment_count() RETURNS trigger
LANGUAGE plpgsql SET search_path = public AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE article SET comment_count = comment_count + 1 WHERE article_id = NEW.article_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE article SET comment_count = comment_count - 1 WHERE article_id = OLD.article_id;
  END IF;
  RETURN NULL;
END $$;

CREATE TRIGGER comment_count_sync AFTER INSERT OR DELETE ON comment
  FOR EACH ROW EXECUTE FUNCTION sync_comment_count();

Kết quả: 5 insert, 1 delete thì comment_count = 4, khớp count(*). 8 phiên song song, mỗi phiên 25 insert: 204 khớp 204, vì UPDATE ... + 1 khoá dòng bài viết và tuần tự hoá các lượt cộng.

Lần chạy đầu, hàm không có SET search_path trong khi bảng nằm ở schema riêng, và mọi insert từ phiên khác lỗi relation "article" does not exist: tên không kèm schema được tìm theo search_path của phiên gọi. Luôn ghim search_path trong hàm trigger.

Mọi bình luận của một bài khoá cùng một dòng article. Bài viral cần đếm theo slot hoặc event cộng theo lô, kèm job đối soát count(*) với cột đếm.

Ghi nhớ

  • Giải thích được vì sao phải viết danh sách access pattern (tần suất, p95) trước khi vẽ ERD.
  • Phân loại được một bảng vào entity, relation, support hay config, và biết mỗi loại cần cột kỹ thuật nào.
  • Biết khi nào thêm cột dư thừa (lọc bảng A, sắp bảng B) và ba điều kiện để làm an toàn.
  • So sánh được khoá dòng cha khi insert dòng con ở InnoDB và PostgreSQL, và khi nào bỏ FK.
  • Biết đặt cột tham chiếu 1–1 ở bảng nào và vì sao sổ cái không cần updated_*, version.

Câu hỏi phỏng vấn

Junior

Entity khác bảng config thế nào?

Entity có danh tính và vòng đời; bảng config là danh mục ít đổi, không có vòng đời đáng kể.

Entity thường có status đổi theo thời gian (đơn hàng, bài viết). Config (quốc gia, tiền tệ) hợp với khoá tự nhiên và cache ở tầng ứng dụng.

Khoá ngoại khác cột tham chiếu thế nào?

Cột tham chiếu là dữ liệu (lưu id dòng cha); khoá ngoại là ràng buộc database đặt lên cột đó để giá trị luôn trỏ tới dòng cha tồn tại.

Bỏ ràng buộc thì cột vẫn còn và vẫn cần index nếu có truy vấn đi theo nó.

Bảng relation cho quan hệ N–M thường có khoá chính thế nào?

Khoá chính ghép từ hai khoá ngoại, ví dụ post_tag (post_id, tag_id).

Nó vừa chống trùng cặp vừa phục vụ truy vấn theo cột đầu. Thêm index (tag_id, post_id) nếu cần chiều ngược lại. Thường chỉ cần thêm created_at, không cần đủ table template.

Mid

Bạn bắt đầu thiết kế schema cho một tính năng mới thế nào?

Viết danh sách truy vấn và lệnh ghi chính trước, mỗi cái có tần suất và mục tiêu độ trễ.

Sau đó gom trường thành entity, phân loại bảng (entity, relation, support, config), chuẩn hoá làm nền, rồi kiểm từng truy vấn nóng xem có index phục vụ được không; chỗ nào không thì cân nhắc phi chuẩn hoá có chủ đích.

Cột tham chiếu của quan hệ 1–1 giữa order và giao dịch thanh toán nên đặt ở bảng nào?

Ở bảng sinh ra sau, tức payment_txn.order_id, kèm UNIQUE để giữ 1–1.

Khi tạo order chưa có giao dịch nên không phải insert rồi update. Thêm loại giao dịch mới (quyên góp không có order) không phải đổi bảng orders.

Vì sao read-modify-write một cột đếm trong Java là sai?

Hai request đọc cùng giá trị, cùng cộng một, cùng ghi, nên mất một lượt (lost update).

Sửa bằng cập nhật nguyên tử SET count = count + 1 trong SQL, hoặc khoá lạc quan @Version để một bên thất bại và thử lại.

Khi nào bạn thêm cột dư thừa vào một bảng đã chuẩn hoá?

Khi một truy vấn nóng lọc ở bảng này nhưng sắp ở bảng kia, nên không composite index nào phục vụ trọn.

Ví dụ feed theo quốc gia của tác giả: thêm author_country vào post và index (author_country, created_at) giảm từ 125.000 buffer xuống 23. Điều kiện: dữ liệu nguồn ít đổi, một cơ chế đồng bộ duy nhất, và job đối soát.

Senior

Bạn có bỏ ràng buộc khoá ngoại không?

Mặc định giữ khi các bảng cùng một database và một service sở hữu: kiểm tra chỉ là một lần tra index, đổi lại không bao giờ có dòng mồ côi.

Bỏ khi cha con ở shard hay service khác, khi cần công cụ migrate như gh-ost, hoặc khi shared lock của InnoDB trên dòng cha gây tranh chấp đo được. Bỏ thì phải có kiểm tra có khoá hoặc job tìm dòng mồ côi; SELECT rồi INSERT không khoá có race condition.

Insert dòng con có khoá ngoại ảnh hưởng gì tới dòng cha?

InnoDB đặt shared lock trên bản ghi cha tới hết transaction, chặn mọi UPDATE vào dòng cha; PostgreSQL chỉ khoá FOR KEY SHARE.

FOR KEY SHARE chặn xoá và đổi khoá nhưng không chặn cập nhật cột thường. Với dòng cha nóng trên MySQL, đây là nguồn chờ khoá và deadlock.

Bảng sổ cái có nên dùng đủ table template không?

Không. Sổ cái chỉ thêm: sai thì ghi bút toán đảo, không sửa.

Chỉ cần created_at, created_by; bỏ updated_* và version vì chúng ngầm cho phép sửa. Biến quy ước thành ràng buộc bằng trigger chặn UPDATE, DELETE và REVOKE quyền đó khỏi role ứng dụng.

Từ vựng tiếng Anh

Term Phát âm Nghĩa Ví dụ câu tiếng Anh dùng trong phỏng vấn
access pattern /ˈækses ˈpætərn/ cách dữ liệu được đọc, ghi “I list the access patterns with their frequency before I draw a single table.”
entity /ˈentəti/ đối tượng có danh tính và vòng đời “An entity has an identity and a lifecycle, usually modeled as a status column.”
denormalize /diːˈnɔːrməlaɪz/ phi chuẩn hoá “We denormalized the author’s country onto posts so one index serves the feed.”
referential integrity /ˌrefəˈrenʃl ɪnˈteɡrəti/ toàn vẹn tham chiếu “Foreign keys give us referential integrity even when someone runs a manual script.”
orphan row /ˈɔːrfn roʊ/ dòng mồ côi “Without the constraint, the race left an orphan row.”
audit columns /ˈɔːdɪt ˈkɑːləmz/ cột ai tạo, ai sửa, khi nào “Every entity table gets audit columns: created_at, created_by, updated_at, updated_by.”
optimistic locking /ˌɑːptɪˈmɪstɪk ˈlɑːkɪŋ/ khoá lạc quan “The version column gives us optimistic locking without holding row locks.”
append-only /əˈpend ˈoʊnli/ chỉ thêm, không sửa “Ledger entries are append-only, so they don’t need updated_at.”

Ghi chép dựa trên khoá Advanced Backend (Ronin Engineer), viết lại theo cách hiểu của mình.