12.2 — 1. Database Design cho CRM

Tóm tắt

Thiết kế schema là quyết định khó sửa nhất trong một hệ thống: code viết lại được trong một chiều, còn di chuyển 50 triệu dòng sang cấu trúc mới thì mất hàng tuần và có rủi ro. Ba nguyên tắc chi phối toàn bộ bài này. Chuẩn hoá tới 3NF trước, phi chuẩn hoá sau khi đo — phi chuẩn hoá sớm là tối ưu mù. Ràng buộc trong database là hợp đồng cuối cùng: tầng ứng dụng có thể bị bỏ qua bởi một script sửa dữ liệu, ràng buộc thì không. Và GUID ngẫu nhiên làm khoá chính gây phân mảnh nghiêm trọng trên index gom cụm — một quyết định trông vô hại nhưng làm chậm mọi thao tác ghi.

Mục tiêu bài học

Sau bài này bạn có thể:

• Chuẩn hoá một schema tới 3NF và biết khi nào đi ngược lại.
• Chọn khoá chính có cân nhắc tới index gom cụm.
• Dùng ràng buộc để bảo vệ dữ liệu ở tầng cuối cùng.
• Thiết kế soft delete mà không phá index.
• Thiết kế audit trail dùng được khi điều tra.

Nội dung bài học

12.2.1 — Schema cốt lõi

CREATE TABLE Users (
    UserId      INT IDENTITY(1,1) PRIMARY KEY,
    Email       NVARCHAR(256) NOT NULL,
    FullName    NVARCHAR(200) NOT NULL,
    Role        NVARCHAR(50)  NOT NULL DEFAULT 'SalesRep',
    IsActive    BIT           NOT NULL DEFAULT 1,
    CreatedAt   DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt   DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    RowVersion  ROWVERSION,

    CONSTRAINT UQ_Users_Email UNIQUE (Email)
);

CREATE TABLE Customers (
    CustomerId       INT IDENTITY(1,1) PRIMARY KEY,
    CompanyName      NVARCHAR(300) NOT NULL,
    Industry         NVARCHAR(100) NULL,
    AnnualRevenue    DECIMAL(18,2) NULL,
    CountryCode      CHAR(2)       NOT NULL DEFAULT 'VN',
    AssignedToUserId INT           NULL,
    Status           NVARCHAR(50)  NOT NULL DEFAULT 'Active',
    CreatedAt        DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt        DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    DeletedAt        DATETIME2(3)  NULL,
    RowVersion       ROWVERSION,

    CONSTRAINT FK_Customers_Users FOREIGN KEY (AssignedToUserId)
        REFERENCES Users(UserId) ON DELETE SET NULL,
    CONSTRAINT CK_Customers_Status CHECK (Status IN ('Active','Inactive','Churned')),
    CONSTRAINT CK_Customers_Revenue CHECK (AnnualRevenue IS NULL OR AnnualRevenue >= 0)
);

Bốn quyết định trong đoạn trên đáng giải thích:

DATETIME2(3) thay vì DATETIME2. Mặc định là độ chính xác 7 chữ số và chiếm 8 byte; 3 chữ số (mili giây) chiếm 7 byte và đủ cho mọi nhu cầu nghiệp vụ. Một byte nhân với 50 triệu dòng là 50MB — và quan trọng hơn là nhiều dòng hơn trên mỗi trang dữ liệu.

SYSUTCDATETIME() thay vì GETUTCDATE(). Cái sau chỉ chính xác tới khoảng 3,33 mili giây do kiểu DATETIME cũ.

ROWVERSION cho optimistic concurrency — mỗi lần sửa dòng, giá trị này tự đổi. EF Core dùng nó để phát hiện xung đột (Module 13).

CountryCode CHAR(2) thay vì CountryName. Lưu tên quốc gia là vi phạm 3NF — mục sau.

12.2.2 — Chuẩn hoá tới 3NF

Ba dạng chuẩn đầu, diễn giải thực dụng:

	Quy tắc	Vi phạm điển hình trong CRM
1NF	Mỗi ô một giá trị nguyên tử	Phone1, Phone2, Phone3 thay vì bảng ContactPhones
2NF	Cột non-key phụ thuộc toàn bộ khoá chính	LeadContacts(LeadId, ContactId, ContactEmail) — email chỉ phụ thuộc ContactId
3NF	Không có phụ thuộc bắc cầu	Customers(CustomerId, CountryCode, CountryName) — CountryName phụ thuộc CountryCode

Vi phạm 1NF phổ biến nhất là nhồi danh sách vào một cột: Tags NVARCHAR(500) chứa "vip,enterprise,hanoi". Nó trông tiện cho tới khi bạn cần tìm mọi khách hàng có tag vip — LIKE '%vip%' cũng khớp vip-cancelled, và không index nào dùng được.

Vi phạm 3NF ở ví dụ trên gây ra vấn đề rất cụ thể: Việt Nam đổi tên tiếng Anh, và bạn phải UPDATE 2 triệu dòng. Với bảng Countries riêng, đó là một dòng.

Khi nào phi chuẩn hoá:

Tình huống	Có nên
"Bảng này chắc sẽ chậm"	Không — đo trước
Đã đo, JOIN 5 bảng mất 800ms, chạy 10.000 lần/phút	Có
Bảng báo cáo, dữ liệu làm mới theo lô	Có
Ảnh chụp lịch sử (giá lúc đặt hàng)	Có — và đây không phải phi chuẩn hoá

Hàng cuối hay bị nhầm. OrderLines.UnitPrice phải lưu giá tại thời điểm đặt, không JOIN sang Products.Price — vì giá sản phẩm đổi và hoá đơn cũ phải giữ nguyên. Đó là dữ liệu khác nhau về ngữ nghĩa, không phải bản sao thừa.

Khi phi chuẩn hoá thật, ghi lại lý do:

-- PHI CHUAN HOA CO CHU DICH:
-- ContactCount được cập nhật bởi trigger trên Contacts.
-- Lý do: màn hình danh sách khách hàng gọi COUNT(*) 40.000 lần/phút, mất 1,2 giây.
-- Do luc: 2026-03-15. Xem ticket CRM-1842.
ALTER TABLE Customers ADD ContactCount INT NOT NULL DEFAULT 0;

Không có comment này, người sau sẽ thấy một cột trùng lặp và hoặc xoá nó, hoặc để nó lệch dần với sự thật.

12.2.3 — Khoá chính và index gom cụm

Trong SQL Server, khoá chính mặc định là index gom cụm — nghĩa là dữ liệu thật được sắp xếp vật lý theo nó.

-- TỐT: giá trị tăng dần -> ghi luôn ở cuối, không tách trang
CustomerId INT IDENTITY(1,1) PRIMARY KEY

-- TỆ: giá trị ngẫu nhiên -> chèn vào GIỮA, tách trang liên tục
CustomerId UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY

Với GUID ngẫu nhiên, mỗi lần chèn rơi vào một vị trí ngẫu nhiên trong index. Trang đầy thì SQL Server tách trang: nửa dữ liệu chuyển sang trang mới. Hệ quả là phân mảnh cao, trang chỉ đầy khoảng một nửa, và mọi thao tác đọc phải nạp nhiều trang hơn.

Thêm nữa: mỗi index không gom cụm đều chứa khoá gom cụm để trỏ về dòng. GUID 16 byte thay vì INT 4 byte làm mọi index phụ phình lên.

Chiến lược	Khi dùng
INT IDENTITY	Mặc định. Dưới 2,1 tỷ dòng
BIGINT IDENTITY	Bảng log, bảng sự kiện
NEWSEQUENTIALID() / UUIDv7	Cần GUID và vẫn muốn ghi tuần tự
GUID ngẫu nhiên	Tránh làm khoá gom cụm

Cần GUID cho API công khai nhưng vẫn muốn ghi tốt? Dùng cả hai:

CustomerId INT IDENTITY(1,1) PRIMARY KEY,                 -- gom cum, noi bo
PublicId   UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),     -- lo ra API

CONSTRAINT UQ_Customers_PublicId UNIQUE NONCLUSTERED (PublicId)

Khoá ngoại và JOIN dùng INT; API dùng PublicId nên không ai đoán được bạn có bao nhiêu khách hàng.

12.2.4 — Ràng buộc là hợp đồng cuối cùng

Validation ở tầng ứng dụng bị bỏ qua bởi: script sửa dữ liệu thủ công, job import, một service khác nối vào cùng database, và chính bạn lúc 2 giờ sáng đang chữa cháy.

-- NOT NULL: cột này luôn phải có giá trị
Email NVARCHAR(256) NOT NULL

-- UNIQUE: không trùng — và tự tạo index
CONSTRAINT UQ_Users_Email UNIQUE (Email)

-- CHECK: giá trị hợp lệ
CONSTRAINT CK_Leads_Status CHECK (Status IN ('New','Contacted','Qualified','Won','Lost'))
CONSTRAINT CK_Leads_Value  CHECK (EstimatedValue IS NULL OR EstimatedValue >= 0)

-- CHECK nhiều cột
CONSTRAINT CK_Leads_Converted CHECK (
    (ConvertedAt IS NULL AND ConvertedToCustomerId IS NULL) OR
    (ConvertedAt IS NOT NULL AND ConvertedToCustomerId IS NOT NULL)
)

Ràng buộc cuối cùng đáng chú ý: nó biến một bất biến nghiệp vụ — "đã convert thì phải có customer id" — thành thứ database không cho phép vi phạm. Không script nào, không bug nào tạo được trạng thái nửa vời đó.

Khoá ngoại với hành vi xoá đúng ngữ nghĩa:

-- CASCADE: contact không có nghĩa nếu không có customer
FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId) ON DELETE CASCADE

-- SET NULL: lead vẫn tồn tại dù nhân viên nghỉ việc
FOREIGN KEY (AssignedToUserId) REFERENCES Users(UserId) ON DELETE SET NULL

-- NO ACTION (mặc định): không cho xoá nếu còn tham chiếu
FOREIGN KEY (ProductId) REFERENCES Products(ProductId)

Cẩn thận với CASCADE nhiều tầng: xoá một Customer kéo theo Contacts, rồi ContactNotes, rồi… Một lệnh DELETE tưởng nhỏ có thể xoá hàng chục nghìn dòng và giữ khoá rất lâu. SQL Server còn từ chối tạo nhiều đường cascade cùng tới một bảng.

12.2.5 — Soft delete

DeletedAt DATETIME2(3) NULL     -- NULL = chưa xoá

DateTime? thay vì bool IsDeleted cho bạn cả hai thông tin: đã xoá chưa, và xoá lúc nào.

Nhưng soft delete phá vỡ ràng buộc UNIQUE: xoá mềm a@example.com rồi tạo lại cùng email sẽ vi phạm unique, vì dòng cũ vẫn còn.

-- SQL Server: filtered index
CREATE UNIQUE INDEX UQ_Users_Email_Active
    ON Users(Email) WHERE DeletedAt IS NULL;

-- PostgreSQL: partial index
CREATE UNIQUE INDEX uq_users_email_active
    ON users(email) WHERE deleted_at IS NULL;

Chỉ những dòng chưa xoá mới phải duy nhất. Filtered index còn nhỏ hơn và nhanh hơn index đầy đủ.

Soft delete còn ba hệ quả nữa:

1. Mọi truy vấn phải lọc DeletedAt IS NULL — quên một chỗ là dữ liệu đã xoá hiện lại. Dùng global query filter của EF Core (bài 10.7).
2. Khoá ngoại vẫn giữ dòng đã xoá mềm, nên bạn có thể tham chiếu tới thứ "đã xoá".
3. Bảng không bao giờ nhỏ lại. Cần một job chuyển dữ liệu đã xoá quá lâu sang bảng lưu trữ.

Nếu không có yêu cầu khôi phục hay tuân thủ, xoá cứng đơn giản hơn nhiều. Đừng mặc định soft delete cho mọi bảng.

12.2.6 — Audit trail

CREATE TABLE AuditLog (
    AuditId     BIGINT IDENTITY(1,1) PRIMARY KEY,
    TableName   NVARCHAR(128) NOT NULL,
    RecordId    NVARCHAR(50)  NOT NULL,
    Action      CHAR(1)       NOT NULL,        -- I, U, D
    ChangedBy   INT           NULL,
    ChangedAt   DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    OldValues   NVARCHAR(MAX) NULL,            -- JSON
    NewValues   NVARCHAR(MAX) NULL,

    CONSTRAINT CK_AuditLog_Action CHECK (Action IN ('I','U','D')),
    INDEX IX_AuditLog_Record (TableName, RecordId, ChangedAt DESC)
);

Ba cách cài, mỗi cách một đánh đổi:

Cách	Ưu	Nhược
Trigger	Bắt mọi thay đổi, kể cả từ script thủ công	Khó debug, không biết ai sửa (không có ngữ cảnh ứng dụng)
Tầng ứng dụng (EF interceptor)	Biết người dùng nào, dễ test	Bỏ sót thay đổi không đi qua ứng dụng
Temporal table (SQL Server 2016+)	Database tự quản, truy vấn theo thời điểm	Không ghi được "ai sửa"

Kết hợp thường là câu trả lời: temporal table cho lịch sử đầy đủ, cộng một cột ModifiedBy để biết ai.

ALTER TABLE Customers ADD
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT SYSUTCDATETIME(),
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL DEFAULT '9999-12-31 23:59:59.9999999',
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE Customers
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomersHistory));

Sau đó truy vấn trạng thái bảng tại bất kỳ thời điểm nào:

SELECT * FROM Customers
FOR SYSTEM_TIME AS OF '2026-03-15 10:00:00'
WHERE CustomerId = 42;

Đây là câu trả lời trực tiếp cho "dữ liệu này hôm qua là gì" — không cần tự dựng gì.

12.2.7 — Rà lại code của bạn

Danh sách rà soát thiết kế schema

• •Không có cột nào chứa danh sách phân tách bằng dấu phẩy.
• •Không lưu cả mã và tên của cùng một thực thể tham chiếu.
• •Mọi phi chuẩn hoá đều có comment ghi lý do và số đo.
• •Khoá chính không phải GUID ngẫu nhiên trên index gom cụm.
• •Cần GUID cho API thì dùng cột riêng, không làm khoá chính.
• •Mọi bất biến nghiệp vụ quan trọng có CHECK constraint tương ứng.
• •Khoá ngoại có hành vi xoá đúng ngữ nghĩa, không mặc định CASCADE.
• •Không có chuỗi cascade nhiều tầng có thể xoá hàng loạt ngoài ý muốn.
• •Soft delete đi kèm filtered index cho ràng buộc UNIQUE.
• •Mọi truy vấn trên bảng có soft delete đều lọc bản ghi đã xoá.
• •Bảng tiền tệ dùng DECIMAL, không dùng FLOAT.
• •Có audit trail cho bảng nhạy cảm, biết cả ai sửa và sửa gì.

Bài tập áp dụng

Bài 1 — Đo phân mảnh do khoá chính ngẫu nhiên

Tạo hai bảng giống hệt nhau, một khoá chính INT IDENTITY, một UNIQUEIDENTIFIER DEFAULT NEWID(). Chèn 500.000 dòng vào mỗi bảng, đo thời gian chèn và chạy sys.dm_db_index_physical_stats để so sánh phân mảnh.

Tiêu chí hoàn thành: bạn giải thích được vì sao giá trị ngẫu nhiên gây phân mảnh còn giá trị tăng dần thì không.

Gợi ý và lời giải — Bài 1

Gợi ý. Clustered index giữ các dòng đã sắp xếp theo thứ tự vật lý trên đĩa. Hãy nghĩ xem chèn một dòng vào giữa một trang đã đầy thì chuyện gì xảy ra.

Lời giải — dựng hai bảng:

CREATE TABLE LeadsInt (
    Id        INT IDENTITY(1,1)  PRIMARY KEY CLUSTERED,
    Name      NVARCHAR(200)      NOT NULL,
    Value     DECIMAL(18,2)      NOT NULL,
    CreatedAt DATETIME2(3)       NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE LeadsGuid (
    Id        UNIQUEIDENTIFIER   PRIMARY KEY CLUSTERED DEFAULT NEWID(),
    Name      NVARCHAR(200)      NOT NULL,
    Value     DECIMAL(18,2)      NOT NULL,
    CreatedAt DATETIME2(3)       NOT NULL DEFAULT SYSUTCDATETIME()
);

SET STATISTICS TIME ON;

INSERT INTO LeadsInt (Name, Value)
SELECT TOP (500000) CONCAT(N'Khách hàng ', n), n * 1000
FROM   (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
        FROM sys.all_objects a CROSS JOIN sys.all_objects b) x;

INSERT INTO LeadsGuid (Name, Value)
SELECT TOP (500000) CONCAT(N'Khách hàng ', n), n * 1000
FROM   (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
        FROM sys.all_objects a CROSS JOIN sys.all_objects b) x;

Đo phân mảnh:

SELECT OBJECT_NAME(ips.object_id)  AS TableName,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.avg_page_space_used_in_percent
FROM   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
WHERE  ips.index_level = 0
  AND  OBJECT_NAME(ips.object_id) IN ('LeadsInt', 'LeadsGuid');

Kết quả điển hình trên SQL Server với 500.000 dòng:

	INT IDENTITY	UNIQUEIDENTIFIER
Thời gian chèn	~4 giây	~11 giây
Phân mảnh	< 1%	~99%
Số trang	~5.900	~9.400
Độ đầy trang trung bình	~99%	~62%

Vì sao giá trị tăng dần không gây phân mảnh. Clustered index xếp các dòng theo đúng thứ tự khoá trên các trang 8 KB. Với khoá tăng dần, mỗi dòng mới luôn lớn hơn mọi dòng đã có, nên nó được ghi vào cuối trang cuối cùng. Trang đầy thì cấp một trang mới và ghi tiếp — không dòng nào phải dịch chuyển.

Vì sao giá trị ngẫu nhiên gây phân mảnh. NEWID() cho một giá trị rơi vào bất kỳ vị trí nào trong dải đã có. Nếu trang chứa vị trí đó đã đầy, SQL Server phải tách trang: cấp một trang mới và chuyển khoảng một nửa số dòng sang đó.

Trước:  [ 10 | 25 | 40 | 55 | 70 | 85 | 90 | 95 ]   trang đầy
Chèn 60:
Sau:    [ 10 | 25 | 40 | 55 ]  [ 60 | 70 | 85 | 90 | 95 ]
         trang cũ, còn nửa      trang mới

Mỗi lần tách sinh ra hai trang chỉ đầy khoảng 50–60%, và các trang không còn nằm liền nhau trên đĩa. Với 500.000 lần chèn ngẫu nhiên, điều này xảy ra liên tục — giải thích cả ba con số ở bảng trên: chèn chậm hơn (vì phải tách trang), nhiều trang hơn (vì mỗi trang chỉ đầy 62%), và phân mảnh gần 100%.

Hệ quả khi đọc: cùng một lượng dữ liệu nhưng phải đọc nhiều trang hơn 60%, và chúng nằm rải rác nên mất lợi thế đọc tuần tự.

Ba cách giữ được GUID mà không chịu phân mảnh:

-- 1. NEWSEQUENTIALID — GUID tăng dần trong phạm vi một máy chủ
Id UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED DEFAULT NEWSEQUENTIALID()

// 2. UUID v7 — có timestamp ở đầu, tăng dần, sinh được ở phía ứng dụng
Id = Guid.CreateVersion7()          // .NET 9+

-- 3. Tách hai vai: khoá chính tăng dần cho lưu trữ, GUID cho định danh công khai
CREATE TABLE Leads (
    Id        INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    PublicId  UNIQUEIDENTIFIER  NOT NULL DEFAULT NEWID() UNIQUE NONCLUSTERED,
    ...
);

Cách 2 thường là lựa chọn tốt nhất hiện nay: ứng dụng tự sinh id (không cần round-trip để lấy id sau khi chèn), id không đoán được từ bên ngoài, mà vẫn tăng dần nên không phân mảnh. Xem thêm bài 13.3 về strongly-typed ID.

---

Bài 2 — Soft delete phá vỡ ràng buộc UNIQUE

Tạo UNIQUE(Email) thường, xoá mềm một người dùng rồi tạo lại cùng email. Ghi lại lỗi. Chuyển sang filtered index và kiểm chứng.

Tiêu chí hoàn thành: bạn nêu được vì sao ràng buộc UNIQUE thường không thể biết về khái niệm "đã xoá".

Gợi ý và lời giải — Bài 2

Gợi ý. Với database, DeletedAt chỉ là một cột DATETIME2 như mọi cột khác. Nó không mang ý nghĩa đặc biệt nào.

Lời giải — ràng buộc thường:

CREATE TABLE Users (
    Id        INT IDENTITY(1,1) PRIMARY KEY,
    Email     NVARCHAR(256)     NOT NULL UNIQUE,
    DeletedAt DATETIME2(3)      NULL
);

INSERT INTO Users (Email) VALUES (N'an@company.com');

-- Xoá mềm
UPDATE Users SET DeletedAt = SYSUTCDATETIME() WHERE Email = N'an@company.com';

-- Người đó quay lại, đăng ký lại
INSERT INTO Users (Email) VALUES (N'an@company.com');

Msg 2627, Level 14, State 1
Violation of UNIQUE KEY constraint 'UQ__Users__A9D105345F1A2B3C'.
Cannot insert duplicate key in object 'dbo.Users'. The duplicate key value is (an@company.com).

Câu trả lời cho tiêu chí. Ràng buộc UNIQUE thực thi một quy tắc đơn giản: không có hai dòng nào trong bảng mang cùng giá trị ở cột đó. Nó không biết DeletedAt là gì — với nó, đó chỉ là một cột dữ liệu.

Khái niệm "đã xoá" là một quy ước của ứng dụng, không phải của database. Dòng bị xoá mềm vẫn nằm nguyên trong bảng, nên nó vẫn tham gia vào ràng buộc. Muốn database hiểu quy ước đó, bạn phải nói cho nó biết — bằng một index có điều kiện.

Filtered index:

ALTER TABLE Users DROP CONSTRAINT UQ__Users__A9D105345F1A2B3C;

CREATE UNIQUE INDEX UX_Users_Email_Active
    ON Users(Email)
    WHERE DeletedAt IS NULL;          -- chỉ áp cho dòng CHƯA xoá

INSERT INTO Users (Email) VALUES (N'an@company.com');
-- (1 row affected)

SELECT Id, Email, DeletedAt FROM Users;

Id  Email             DeletedAt
1   an@company.com    2026-09-25 09:14:22.104
2   an@company.com    NULL

Hai dòng cùng email, và ràng buộc vẫn đúng — vì chỉ có một dòng đang hoạt động.

Trên PostgreSQL cú pháp gần như giống hệt:

CREATE UNIQUE INDEX ux_users_email_active
    ON users(email)
    WHERE deleted_at IS NULL;

Ba điều cần biết về filtered index:

1. Nó vừa là ràng buộc vừa là index. Truy vấn WHERE Email = @e AND DeletedAt IS NULL dùng được nó, và vì index chỉ chứa dòng đang hoạt động nên nó nhỏ hơn index đầy đủ.

2. Truy vấn phải khớp điều kiện lọc mới dùng được. WHERE Email = @e không có AND DeletedAt IS NULL thì SQL Server không dùng index này — nó không thể chắc rằng kết quả đầy đủ.

3. EF Core khai báo được bằng Fluent API:

   modelBuilder.Entity<User>()
       .HasIndex(u => u.Email)
       .IsUnique()
       .HasFilter("[DeletedAt] IS NULL");

   Và kết hợp với global query filter thì mọi truy vấn tự mang điều kiện đó — xem bài 13.11.

Một lựa chọn thay thế khi database không hỗ trợ filtered index (MySQL chẳng hạn): đưa trạng thái vào chính khoá.

-- Cột tính toán: NULL khi chưa xoá, giá trị khi đã xoá
DeletedFlag AS (CASE WHEN DeletedAt IS NULL THEN NULL ELSE Id END) PERSISTED,
UNIQUE (Email, DeletedFlag)

Cách này xấu hơn và khó đọc hơn, nên chỉ dùng khi buộc phải.

---

Bài 3 — Temporal table: lấy lại trạng thái quá khứ

Bật system versioning cho một bảng, sửa dữ liệu vài lần, rồi truy vấn FOR SYSTEM_TIME AS OF để lấy lại trạng thái cũ.

Tiêu chí hoàn thành: bạn nêu được temporal table giải quyết được gì mà một bảng lịch sử tự viết thì không.

Gợi ý và lời giải — Bài 3

Gợi ý. Bảng lịch sử tự viết dựa vào việc mọi đường ghi đều nhớ ghi lịch sử. Hãy nghĩ tới đường ghi mà bạn quên.

Lời giải — bật system versioning:

CREATE TABLE Leads (
    Id        INT IDENTITY(1,1) PRIMARY KEY,
    Name      NVARCHAR(200)     NOT NULL,
    Value     DECIMAL(18,2)     NOT NULL,
    Status    NVARCHAR(50)      NOT NULL,

    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LeadsHistory));

Sửa dữ liệu vài lần:

INSERT INTO Leads (Name, Value, Status) VALUES (N'Công ty ABC', 5000000, N'New');
WAITFOR DELAY '00:00:02';

UPDATE Leads SET Value = 8000000 WHERE Id = 1;
WAITFOR DELAY '00:00:02';

UPDATE Leads SET Status = N'Won' WHERE Id = 1;

Lấy lại trạng thái tại một thời điểm:

-- Dữ liệu tại MỘT thời điểm trong quá khứ
SELECT * FROM Leads
FOR SYSTEM_TIME AS OF '2026-09-25 09:14:20'
WHERE Id = 1;

Id  Name           Value      Status
1   Công ty ABC    5000000    New

Toàn bộ lịch sử thay đổi của một dòng:

SELECT Value, Status, ValidFrom, ValidTo
FROM   Leads
FOR SYSTEM_TIME ALL
WHERE  Id = 1
ORDER  BY ValidFrom;

Value     Status  ValidFrom                ValidTo
5000000   New     2026-09-25 09:14:18.12   2026-09-25 09:14:20.34
8000000   New     2026-09-25 09:14:20.34   2026-09-25 09:14:22.56
8000000   Won     2026-09-25 09:14:22.56   9999-12-31 23:59:59.99

Điều temporal table làm được mà bảng lịch sử tự viết thì không: nó không thể bị bỏ sót.

Bảng lịch sử tự viết sống nhờ việc mọi đường ghi đều nhớ ghi lịch sử:

// Đường 1 — nhớ ghi lịch sử
lead.Value = newValue;
_db.LeadHistory.Add(new LeadHistory { ... });

// Đường 2 — quên
await _db.Leads.Where(l => l.Status == "Stale")
    .ExecuteUpdateAsync(s => s.SetProperty(l => l.Status, "Lost"), ct);

// Đường 3 — script chạy tay lúc 2 giờ sáng để sửa dữ liệu
UPDATE Leads SET Value = 0 WHERE Id IN (...);

Đường 2 và 3 không đi qua code ghi lịch sử của bạn, nên lịch sử thiếu đúng những thay đổi khó giải thích nhất. Và bạn chỉ phát hiện điều đó khi cần tra cứu — tức là lúc đã muộn.

Temporal table chạy ở tầng database engine, nên nó ghi lại mọi UPDATE và DELETE bất kể chúng đến từ đâu: EF Core, Dapper, script chạy tay, hay một job của đội khác.

Truy vấn từ EF Core:

var snapshot = await _db.Leads
    .TemporalAsOf(new DateTime(2026, 9, 25, 9, 14, 20, DateTimeKind.Utc))
    .Where(l => l.Id == 1)
    .ToListAsync(ct);

var history = await _db.Leads
    .TemporalAll()
    .Where(l => l.Id == 1)
    .OrderBy(l => EF.Property<DateTime>(l, "ValidFrom"))
    .ToListAsync(ct);

Ba cái giá phải trả:

	Chi phí
Dung lượng	Mỗi UPDATE sinh một dòng trong bảng lịch sử — bảng ghi nhiều có thể phình rất nhanh
Thay đổi schema	Đổi cột phải tắt versioning, sửa cả hai bảng, rồi bật lại
Dọn dẹp	Cần chính sách giữ lịch sử bao lâu, nếu không thì nó lớn mãi

-- Giữ lịch sử 2 năm, SQL Server tự dọn
ALTER TABLE Leads SET (SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = dbo.LeadsHistory,
    HISTORY_RETENTION_PERIOD = 2 YEARS
));

Khi nào nên dùng: bảng mang dữ liệu cần truy vết — hợp đồng, giá, quyền, trạng thái đơn hàng. Khi nào không: bảng log, bảng cache, bảng ghi rất nhiều mà giá trị lịch sử thấp.

PostgreSQL không có tính năng tương đương sẵn; ở đó người ta dùng extension như temporal_tables hoặc tự viết trigger — và lúc đó bạn quay lại với rủi ro "trigger bị tắt lúc nào không biết".

Tự kiểm tra

Câu hỏi thường gặp

Vì sao không nên phi chuẩn hoá sớm?

Vì đó là tối ưu mù: bạn thêm dữ liệu trùng lặp và nguy cơ lệch nhau để giải quyết một vấn đề chưa chắc tồn tại. Nguyên tắc là chuẩn hoá tới 3NF trước, đo, rồi mới phi chuẩn hoá đúng chỗ thắt nút, và luôn ghi lại lý do cùng số đo trong comment.

Lưu giá sản phẩm trong dòng đơn hàng có phải phi chuẩn hoá không?

Không. Giá tại thời điểm đặt hàng và giá hiện tại của sản phẩm là hai dữ liệu khác nhau về ngữ nghĩa, vì giá sản phẩm đổi mà hoá đơn cũ phải giữ nguyên. Đó là ảnh chụp lịch sử chứ không phải bản sao thừa.

Vì sao GUID ngẫu nhiên làm khoá chính lại tệ?

Vì khoá chính mặc định là index gom cụm, tức dữ liệu được sắp xếp vật lý theo nó. Giá trị ngẫu nhiên khiến mỗi lần chèn rơi vào vị trí ngẫu nhiên, gây tách trang liên tục, phân mảnh cao và trang chỉ đầy khoảng một nửa. Thêm nữa, 16 byte thay vì 4 byte làm mọi index phụ phình lên vì chúng đều chứa khoá gom cụm.

Vì sao ràng buộc trong database quan trọng khi ứng dụng đã validate?

Vì validation ở tầng ứng dụng bị bỏ qua bởi script sửa dữ liệu thủ công, job import, một service khác nối vào cùng database, và chính bạn lúc đang chữa cháy. Ràng buộc là hợp đồng cuối cùng mà không đường nào đi vòng được.

Soft delete phá vỡ ràng buộc UNIQUE như thế nào?

Xoá mềm một bản ghi rồi tạo lại cùng giá trị sẽ vi phạm unique vì dòng cũ vẫn còn trong bảng. Cách sửa là filtered index trên SQL Server hoặc partial index trên PostgreSQL, chỉ áp ràng buộc cho những dòng chưa xoá.

Ba cách cài audit trail khác nhau ra sao?

Trigger bắt mọi thay đổi kể cả từ script thủ công nhưng khó debug và không biết ai sửa. Tầng ứng dụng biết người dùng nào nhưng bỏ sót thay đổi không đi qua ứng dụng. Temporal table để database tự quản và truy vấn được theo thời điểm nhưng không ghi được ai sửa. Kết hợp temporal table với một cột ModifiedBy thường là câu trả lời.

Kết luận

Ba điều đáng nhớ nhất:

1. Chuẩn hoá trước, đo, rồi mới phi chuẩn hoá — và ghi lại lý do.
2. GUID ngẫu nhiên làm khoá gom cụm là quyết định đắt và rất khó sửa về sau.
3. Ràng buộc database là hợp đồng cuối cùng. Validation ứng dụng luôn có đường đi vòng.

Tham khảo

• Primary and foreign key constraints (SQL Server)
• Temporal tables (SQL Server)
• Create filtered indexes
• Database normalization description

Điều hướng

• Bài trước: Không có (bài mở đầu module).
• Bài tiếp theo: 12.2 — 2. SQL Server so với PostgreSQL
• Về module: Trang mục lục