บทที่ 6 — Schema Design และ Normalization

← บทที่ 5 | สารบัญ | บทที่ 7 →

หลังจบบท คุณจะ:

• ออกแบบ schema ได้อย่างมีหลัก (1NF, 2NF, 3NF, BCNF)
• รู้ว่าเมื่อไหร่ควร denormalize (อ่าน ดี-นอร์-มอ-ไลซ์ = "ย้อนคืนสภาพปกติ" จงใจให้ข้อมูลซ้ำเพื่อลด JOIN)
• เข้าใจ trade-off ของ design choices
• เลือก data type ถูก
• ทำ migration (อ่าน ไม-เกร-ชั่น = การย้าย/อัปเดต schema) ที่ปลอดภัย

📖 คำอ่าน NF: NF ย่อจาก Normal Form (รูปแบบมาตรฐาน) อ่านตามตัวเลขนำ: 1NF = วัน-เอ็น-เอฟ, 2NF = ทู-เอ็น-เอฟ, 3NF = ทรี-เอ็น-เอฟ, BCNF = บี-ซี-เอ็น-เอฟ (Boyce-Codd Normal Form — เข้มกว่า 3NF เล็กน้อย).

---

1. ปัญหาก่อนมี Normalization

table: students (denormalized)
┌─────┬─────────┬──────────────┬──────────────┬──────────────┐
│ id  │ name    │ courses      │ instructor   │ instr_email  │
├─────┼─────────┼──────────────┼──────────────┼──────────────┤
│ 1   │ Anna    │ Math, Eng    │ Dr. A, Dr. B │ a@x, b@x     │
│ 2   │ Ben     │ Math, Phys   │ Dr. A, Dr. C │ a@x, c@x     │
└─────┴─────────┴──────────────┴──────────────┴──────────────┘

ปัญหา:

• ❌ ค่าหลายค่าใน 1 cell — query ลำบาก
• ❌ ข้อมูล instructor ซ้ำ
• ❌ ลบ student ลบทุกอย่าง — ถ้าเป็น instructor เดียวที่สอน → หาย
• ❌ เพิ่ม course ใหม่ที่ไม่มี student → ใส่ไหน?

→ Normalization = ปรับ schema ให้ลด redundancy + dependency

---

2. Normal Forms (NF)

1NF — Atomic Values

ทุก cell มีค่าเดียว (atomic)

❌ Not 1NF:

phone: "081-xxx, 089-yyy"
courses: "Math, English"

✅ 1NF:

phones table: (user_id, phone)
user_courses table: (user_id, course_id)

2NF — Eliminate Partial Dependency

partial dependency (การพึ่งพาบางส่วน) = กรณีที่ PK ประกอบจากหลาย column แล้วมี column อื่นที่ขึ้นกับ PK "แค่บางส่วน" ไม่ใช่ทั้งก้อน (เช่น product_name ขึ้นกับ product_id อย่างเดียว ทั้งที่ PK = order_id + product_id)

ทุก non-key column depend on entire PK (ไม่ใช่บางส่วน)

❌ Not 2NF (PK = order_id + product_id):

order_items:
┌──────────┬────────────┬──────────┬─────────────┐
│ order_id │ product_id │ quantity │ product_name│  ← depend on product_id อย่างเดียว
├──────────┼────────────┼──────────┼─────────────┤
│ 1        │ 100        │ 2        │ iPhone      │
│ 2        │ 100        │ 1        │ iPhone      │  ← name ซ้ำ
└──────────┴────────────┴──────────┴─────────────┘

✅ 2NF:

products: (id, name, ...)
order_items: (order_id, product_id, quantity)

3NF — Eliminate Transitive Dependency

transitive dependency (การพึ่งพาแบบส่งต่อ) = column หนึ่งขึ้นกับ PK ผ่าน column อื่นที่ไม่ใช่ key (A → B → C เช่น dept_name ขึ้นกับ dept_id ที่ขึ้นกับ employee อีกที) → ควรแยกออกไปอีกตาราง

Non-key column ไม่ depend บน non-key อื่น

❌ Not 3NF:

employees:
┌────┬───────┬───────────┬─────────────┐
│ id │ name  │ dept_id   │ dept_name   │  ← dept_name depend on dept_id, not employee
├────┼───────┼───────────┼─────────────┤
│ 1  │ Anna  │ 10        │ Engineering │
│ 2  │ Ben   │ 10        │ Engineering │  ← ซ้ำ
└────┴───────┴───────────┴─────────────┘

✅ 3NF:

departments: (id, name)
employees: (id, name, dept_id)

กฎจำง่ายของ 3NF:

สรุป: 1NF→atomic, 2NF→ขึ้นกับ key ทั้งก้อน (ไม่ partial), 3NF→ขึ้นกับ key เท่านั้น (ไม่ transitive)

มีประโยคติดตลกจำง่าย: "Every non-key column depends on the key, the whole key, and nothing but the key, so help me Codd."

แปล: "ทุก column ที่ไม่ใช่ key ต้องขึ้นกับ key, key ทั้งก้อน, และไม่มีอะไรนอกจาก key"

🃏 ที่มาของมุก: เป็นการเล่นคำกับ คำสาบานในศาลของสหรัฐ ที่พยานต้องสาบานก่อนให้การว่า "I swear to tell the truth, the whole truth, and nothing but the truth, so help me God" (ขอสาบานว่าจะพูดความจริง ความจริงทั้งหมด และไม่มีอะไรนอกจากความจริง — ขอพระเจ้าช่วยด้วย). ในประโยคนี้ "God" ถูกเปลี่ยนเป็น "Codd" (E.F. Codd นักวิจัย IBM ผู้คิดทฤษฎี relational model ปี 1970) — เป็นมุกที่นัก DBA ใช้กันกันมานานหลายสิบปี

BCNF — เข้มกว่า 3NF เล็กน้อย

BCNF (อ่าน บี-ซี-เอ็น-เอฟ = Boyce-Codd Normal Form) เข้มกว่า 3NF: ทุก determinant (สิ่งที่กำหนดค่า column อื่น) ต้องเป็น superkey ไม่ใช่แค่ "ไม่ transitive" — กรณีหายาก ส่วนใหญ่ 3NF ก็ครอบ BCNF ไปด้วย

ส่วนใหญ่ — 3NF เพียงพอ สำหรับ app ทั่วไป

---

3. ตัวอย่างเต็ม — ก่อน/หลัง Normalize

ก่อน (Denormalized)

CREATE TABLE orders_flat (
    order_id INTEGER,
    customer_name VARCHAR(200),
    customer_email VARCHAR(200),
    customer_phone VARCHAR(50),
    product_name VARCHAR(200),
    product_price NUMERIC(10, 2),
    quantity INTEGER,
    total NUMERIC(10, 2),
    order_date TIMESTAMPTZ
);

ปัญหา: ข้อมูล customer + product ซ้ำทุก order

หลัง (Normalized — 3NF)

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    email VARCHAR(200) UNIQUE NOT NULL,
    phone VARCHAR(50)
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price_at_purchase NUMERIC(10, 2) NOT NULL,    -- ⭐ snapshot ตอนซื้อ
    
    PRIMARY KEY (order_id, product_id)
);

💡 price_at_purchase = snapshot ราคาตอนซื้อ — ถ้าเปลี่ยน products.price ภายหลัง order เก่ายังถูก
ไม่ใช่ denormalize ผิด — เป็น historical fact

---

4. Denormalization — เมื่อไหร่ควรทำ

Normalize 3NF = ดีที่สุด ในแง่ความถูกต้อง
แต่มี cost: JOIN เยอะ → ช้า

-- ต้อง JOIN 4 table แค่ดู order
SELECT 
    o.id, c.name, p.name, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

ถ้า query นี้เร็ว 100 ครั้ง/วินาที + table ใหญ่ → JOIN cost สูง

Denormalize = copy data ที่ใช้บ่อย → ลด JOIN

-- เพิ่ม column ใน orders
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(200);
ALTER TABLE orders ADD COLUMN total NUMERIC(10, 2);

-- update เมื่อ insert + เมื่อเปลี่ยน

✅ Denormalize เมื่อ

• Read-heavy + JOIN เยอะ
• Query ที่ใช้บ่อย — performance สำคัญ
• Data ที่ "เปลี่ยนแล้วเก็บ snapshot" (price_at_purchase, address_at_shipping)
• มี mechanism sync (trigger / event)

❌ Don't denormalize

• เริ่มต้น app — ยังไม่รู้ pattern
• Data เปลี่ยนบ่อย
• ไม่มี measure ว่าช้าจริง

"Premature denormalization is the root of evil" (with apologies to Knuth)
แปล: "การ denormalize ก่อนเวลาอันควร คือต้นตอของหายนะ"

🃏 ที่มา: ล้อประโยคดังของ Donald Knuth (โดนัลด์ คนุท) — นักวิทยาการคอมพิวเตอร์ระดับ legendary ผู้เขียน The Art of Computer Programming และผู้สร้าง TeX/METAFONT, เจ้าของรางวัล Turing Award ปี 1974 — คำคมเดิมคือ "premature optimization is the root of all evil" (การ optimize ก่อนเวลาอันควร = ต้นตอของหายนะทั้งปวง) ใจความคือ อย่ารีบ denormalize ตั้งแต่ยังไม่วัดว่าช้าจริง

Denormalization Trade-offs (สรุป)

ได้	เสีย
Query เร็วขึ้น (JOIN น้อย, อ่าน row เดียวจบ)	Data ซ้ำ → กิน disk + memory เพิ่ม
โหลด DB ฝั่ง read ลดลง	Write ต้องอัปเดตหลายจุด (เสี่ยง inconsistent)
Cache-friendly (row เดียวมีครบ)	Code ฝั่ง write ซับซ้อนขึ้น (trigger / event sync)
รองรับ analytical query เร็ว	Schema migration หนัก (เปลี่ยน column ต้องไล่อัปเดต)

---

5. Surrogate Key vs Natural Key

surrogate key (อ่าน เซอ-โร-เกท คี = กุญแจตัวแทน) = PK ที่สร้างขึ้นมาเองโดยไม่มีความหมายทางธุรกิจ (เช่น id SERIAL ที่นับ 1, 2, 3...) — ใช้เป็นตัวระบุล้วน ๆ
natural key (อ่าน เนเชอ-รัล คี = กุญแจตามธรรมชาติ) = PK ที่ใช้ค่าที่มีความหมายจริงอยู่แล้วและไม่ซ้ำโดยธรรมชาติ (เช่นรหัสประเทศ 'TH', เลข ISBN) — ดีเมื่อค่านั้นนิ่ง ไม่เปลี่ยน

Surrogate (recommended สำหรับ 95% case)

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- ⭐ SQL standard (PG 10+) — แทน BIGSERIAL ที่เป็น legacy
    email VARCHAR(255) UNIQUE NOT NULL,
    ...
);

-- รูปแบบเก่า (ยังใช้ได้ แต่ standard แนะ IDENTITY)
-- id SERIAL PRIMARY KEY,

• ไม่เปลี่ยน
• เล็ก (INTEGER = 4 bytes)
• Index ดี

Natural Key

CREATE TABLE countries (
    code CHAR(2) PRIMARY KEY,     -- 'TH', 'US' — ความหมายธุรกิจ
    name VARCHAR(100)
);

• ใช้ได้ถ้า: stable + small + unique by nature

UUID (อ่าน ยู-ยู-ไอ-ดี หรือ ยู-อิด)

-- ✅ แนะนำใน PG 13+ — ใช้ gen_random_uuid() ที่ built-in ไม่ต้องลง extension
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- UUIDv4 (random)
    ...
);

-- ❌ รูปแบบเก่า — uuid_generate_v4() อยู่ใน extension uuid-ossp ที่ต้องสั่ง
--    CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; ก่อน
--    ไม่งั้น CREATE TABLE จะ fail ว่า "function uuid_generate_v4() does not exist"

ข้อดี:

• Generate ได้ที่ client (ไม่ต้องถาม DB)
• ไม่บอก "เป็น user คนที่กี่"
• Merge data ข้าม DB ได้

ข้อเสีย:

• ใหญ่กว่า INTEGER (16 vs 4 bytes)
• Index ขนาดใหญ่
• Random insert → page split (B-tree ของ index ต้อง split page บ่อย เพราะค่า UUIDv4 สุ่มหมด ทำให้ row ใหม่กระจายไปทั่ว index ไม่เรียงต่อกัน)

🆕 ปี 2026 — UUIDv7 (time-ordered) แก้ปัญหา random insert

UUIDv7 (RFC 9562, พ.ค. 2024) = UUID รุ่นใหม่ที่ขึ้นต้นด้วย 48-bit Unix millisecond timestamp ตามด้วย random bits → ค่าใหม่ มากกว่าเก่าเสมอ (monotonically increasing) → B-tree append ทางขวาเหมือน BIGSERIAL → ไม่ page-split สุ่ม แต่ยัง generate ที่ client ได้

ข้อควรระวัง: gen_random_uuid() ของ PG ยัง generate v4 (random) เท่านั้น. UUIDv7 ต้องใช้ extension pg_uuidv7 หรือ generate ที่ฝั่ง app (Java: Generators.timeBasedEpochGenerator() ของ jackson-uuid, Node: uuidv7 package). สำหรับ schema ใหม่ในปี 2026+ แนะนำ UUIDv7 ตั้งแต่ต้น

---

6. Naming Conventions

การตั้งชื่อที่สม่ำเสมอช่วยให้ schema อ่านง่ายและทำงานร่วมกับ ORM/tool ได้ราบรื่น — convention มาตรฐานของ PostgreSQL คือ snake_case, table พหูพจน์, FK เป็น <table>_id, boolean ขึ้นต้น is_/has_ เลือก convention แล้วใช้ให้เหมือนกันทั้ง schema:

✅ snake_case
   table: users, order_items, blog_posts
   column: created_at, user_id, is_active

❌ camelCase / PascalCase
   table: OrderItems
   column: createdAt

✅ plural table
   users, orders, products

✅ singular column
   email (not emails)

✅ Foreign key = table_singular + _id
   user_id, product_id

✅ Boolean = is_/has_
   is_active, has_admin_access

✅ Timestamp = action_at
   created_at, updated_at, deleted_at

✅ Index name
   idx_<table>_<columns>
   idx_users_email
   idx_orders_user_id_status

---

7. Common Schema Patterns

A. Soft Delete

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

-- Query
SELECT * FROM users WHERE deleted_at IS NULL;

-- Index ที่ partial
CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;

ใช้เมื่อ: ต้องการ undo + audit trail
อย่าใช้เมื่อ: data ขนาดใหญ่ + ไม่มี business need

B. Audit Columns

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
created_by INTEGER REFERENCES users(id),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_by INTEGER REFERENCES users(id)

Trigger สำหรับ updated_at:

📌 PL/pgSQL crash course (อ่าน พี-แอล/พีจี-เอส-คิว-แอล) = ภาษา procedural ของ PostgreSQL สำหรับเขียน function / trigger / DO block

• $$ ... $$ = เครื่องหมายเปิด-ปิด function body (เหมือน quote หลายบรรทัด — ไม่ต้อง escape quote ภายใน)
• RETURNS TRIGGER = function ที่ใช้กับ trigger ต้องคืนค่าเป็น TRIGGER
• OLD / NEW = ใน trigger function — OLD คือ row ก่อนแก้, NEW คือ row หลังแก้
• BEFORE UPDATE = trigger รันก่อน UPDATE จริงจะเกิด (แก้ NEW.* ได้ → DB จะใช้ค่าที่แก้)

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

C. Event Log (Append-only)

CREATE TABLE user_events (
    id BIGSERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ห้าม UPDATE/DELETE — append only

D. Status / State Machine

CREATE TYPE order_status AS ENUM (
    'PENDING', 'CONFIRMED', 'SHIPPED', 'DELIVERED', 'CANCELLED'
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'PENDING',
    ...
);

-- Trigger ตรวจ transition
CREATE OR REPLACE FUNCTION check_status_transition()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.status = 'DELIVERED' AND NEW.status != 'DELIVERED' THEN
        RAISE EXCEPTION 'Cannot change status from DELIVERED';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

E. Hierarchical Data

Option 1: Adjacency List (simple)

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    parent_id INTEGER REFERENCES categories(id)
);

→ ใช้ recursive CTE query

Option 2: Materialized Path

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    parent_id INTEGER REFERENCES categories(id),
    path TEXT NOT NULL          -- '/1/5/12/'
);

-- INSERT: คำนวณ path จาก parent
-- root:
INSERT INTO categories (name, parent_id, path) VALUES ('Electronics', NULL, '/1/');
-- child: ใช้ path ของ parent + id ของ row ใหม่
WITH new_row AS (
    INSERT INTO categories (name, parent_id, path) 
    VALUES ('Phone', 1, '')  -- path ชั่วคราว
    RETURNING id
)
UPDATE categories 
SET path = (SELECT path FROM categories WHERE id = 1) || new_row.id || '/'
FROM new_row
WHERE categories.id = new_row.id;

-- หาทุก child (descendant)
SELECT * FROM categories WHERE path LIKE '/1/5/%';

⚠️ ข้อควรระวัง: ถ้าย้าย parent ต้องอัปเดต path ของลูกหลานทุกตัว → เขียน trigger หรือ application logic ให้ดูแล consistency. ถ้าลืม → path เพี้ยน → query หา child พลาด

Option 3: Nested Set, Closure Table — advanced

F. Versioning / History

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    price NUMERIC(10, 2),
    version INTEGER DEFAULT 1
);

CREATE TABLE products_history (
    id INTEGER,
    version INTEGER,
    name VARCHAR(200),
    price NUMERIC(10, 2),
    changed_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (id, version)
);

-- Trigger save old version
CREATE OR REPLACE FUNCTION save_product_history()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO products_history VALUES
        (OLD.id, OLD.version, OLD.name, OLD.price, NOW());
    NEW.version = OLD.version + 1;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

---

8. Choose Right Data Type

❌	✅
VARCHAR(255) ทุกที่	คิดความยาวจริง (email VARCHAR(254), phone VARCHAR(20))
INT สำหรับ id แล้วเกิน 2.1B	BIGSERIAL สำหรับ table ใหญ่
FLOAT เก็บเงิน	NUMERIC(10, 2)
TIMESTAMP (no tz)	TIMESTAMPTZ
Store JSON เป็น TEXT	JSONB
TEXT สำหรับ enum	CREATE TYPE ... AS ENUM หรือ FK ถึง table
Boolean = INT 0/1	BOOLEAN
Date เป็น VARCHAR	DATE หรือ TIMESTAMPTZ

---

9. Migration Best Practices

migrations/
├── V001__create_users.sql
├── V002__create_orders.sql
├── V003__add_index_users_email.sql
├── V004__rename_users_status.sql

• ใช้ tool migration ตาม ecosystem:
  • Flyway (อ่าน ฟลาย-เวย์) — Java/JVM
  • Liquibase (อ่าน ลิ-ควิ-เบส) — Java/JVM, รองรับ XML/YAML/JSON
  • db-migrate (อ่าน ดี-บี-ไม-เกรท) — Node.js
  • Alembic (อ่าน อะ-เลม-บิก) — Python (มากับ SQLAlchemy)
• 1 file = 1 change
• Forward-only — ไม่แก้ migration ที่ apply แล้ว
• Reversible (เขียน down ด้วย)
• Test ใน staging ก่อน prod

Migration ที่อันตราย (production large table)

-- ❌ Lock ทั้ง table
ALTER TABLE big_table ADD COLUMN new_col VARCHAR(100) NOT NULL DEFAULT 'x';

-- ✅ ทำเป็นขั้น
ALTER TABLE big_table ADD COLUMN new_col VARCHAR(100);             -- 1. add nullable
-- backfill ใน batch
UPDATE big_table SET new_col = 'x' WHERE id BETWEEN 1 AND 100000;
-- ...
ALTER TABLE big_table ALTER COLUMN new_col SET NOT NULL;            -- 4. add constraint

Migration Pitfalls

❌	✅
ลบ column ที่ code ยังใช้	deploy code ใหม่ → migrate ลบ column → ปลอด
Rename column ทันที	add new + dual write → switch read → ลบ old
ALTER ใหญ่ใน prod	online schema change (pg_repack, gh-ost)
ไม่มี backup ก่อน migrate	snapshot ก่อนเสมอ

---

10. Schema Diagram

การวาด ER diagram ช่วยให้เห็นภาพความสัมพันธ์ระหว่างตารางและคุยกับทีมได้ง่ายก่อนลงมือสร้างจริง — มีหลาย tool ทั้งแบบ auto-generate จาก DB (DBeaver), code-to-diagram (dbdiagram.io) และ Mermaid ที่ฝังใน markdown ได้:

ใช้ tool draw ER (Entity Relationship):

• DBeaver — auto generate จาก existing DB
• dbdiagram.io — code → diagram
• drawSQL — visual
• Mermaid — embed ใน markdown

---

11. ตัวอย่างจริง — Design E-commerce Schema

Requirements

• User register + login
• User shop product
• Product has multiple categories
• Order has multiple items
• Track inventory
• Soft delete user

Schema

💡 อ่านยังไง: ตารางหลักไล่ตามลำดับ depend: users → addresses → categories → products → product_categories → orders → order_items → inventory_movements. แต่ละ block มีบรรทัด comment สั้น ๆ บอกบทบาท. รายละเอียดเหตุผลอยู่ใน "Design Decisions" ด้านล่าง

-- Users
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(254) NOT NULL,    -- (ไม่ใส่ UNIQUE ตรงนี้ — ใช้ partial unique index ด้านล่าง)
    password_hash TEXT NOT NULL,    -- ⭐ TEXT แทน VARCHAR(60) → รองรับ algorithm ใดก็ได้
    name VARCHAR(100) NOT NULL,
    is_admin BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);
-- ⭐ partial UNIQUE index → email ซ้ำได้เฉพาะกับ row ที่ soft-deleted แล้ว
-- (เปลี่ยนจาก UNIQUE NOT NULL + partial index ที่ทับซ้อนกัน → เหลือ partial unique index ตัวเดียว)
CREATE UNIQUE INDEX idx_users_email_active ON users(email) WHERE deleted_at IS NULL;

-- Addresses (1 user N address)
CREATE TABLE addresses (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    label VARCHAR(50),
    line1 TEXT NOT NULL,
    city VARCHAR(100),
    country CHAR(2) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Categories (tree)
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    parent_id INTEGER REFERENCES categories(id),
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL
);

-- Products
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Product-Category (M-M)
CREATE TABLE product_categories (
    product_id BIGINT REFERENCES products(id) ON DELETE CASCADE,
    category_id INTEGER REFERENCES categories(id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, category_id)
);

-- Orders
CREATE TYPE order_status AS ENUM (
    'PENDING', 'PAID', 'SHIPPED', 'DELIVERED', 'CANCELLED', 'REFUNDED'
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    status order_status NOT NULL DEFAULT 'PENDING',
    
    -- Snapshot ของ address ตอน order (denormalize)
    shipping_name VARCHAR(100),
    shipping_address TEXT,
    shipping_country CHAR(2),
    
    subtotal NUMERIC(10, 2) NOT NULL,
    shipping NUMERIC(10, 2) NOT NULL DEFAULT 0,
    tax NUMERIC(10, 2) NOT NULL DEFAULT 0,
    total NUMERIC(10, 2) NOT NULL,
    
    created_at TIMESTAMPTZ DEFAULT NOW(),
    paid_at TIMESTAMPTZ,
    shipped_at TIMESTAMPTZ,
    delivered_at TIMESTAMPTZ
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- Order items
CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id),
    
    -- Snapshot
    product_name VARCHAR(200) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    
    UNIQUE (order_id, product_id)
);

-- Inventory movements (audit)
CREATE TABLE inventory_movements (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL REFERENCES products(id),
    change INTEGER NOT NULL,                    -- +10 = receive, -2 = sale
    reason VARCHAR(50) NOT NULL,                 -- 'ORDER', 'RECEIVE', 'ADJUST'
    reference_id BIGINT,                         -- order_id ที่เกี่ยวข้อง
    created_at TIMESTAMPTZ DEFAULT NOW()
);
-- ⭐ index ที่ support query "SUM(change) WHERE product_id = ?" (reconstruct stock)
--    + scan ตามเวลาเรียง — ต้องไม่ลืม
CREATE INDEX idx_inventory_product_time ON inventory_movements(product_id, created_at);

Design Decisions ที่ทำ

1. BIGSERIAL แทน SERIAL — ตาราง user/order/product ที่อาจใหญ่ (เกิน 2.1B = SERIAL overflow)
2. password_hash TEXT — ปี 2026 OWASP แนะนำ Argon2id (รุ่นใหม่กว่า BCrypt) เป็นตัวแรก, BCrypt acceptable. Argon2 hash ยาว ~96+ chars — ถ้า lock VARCHAR(60) = ผูกกับ BCrypt เท่านั้น ใช้ TEXT ยืดหยุ่นกว่า
3. Soft delete user — keep history, partial unique index บน email (กฎ: email unique เฉพาะ user ที่ยัง active. ถ้าใส่ทั้ง UNIQUE NOT NULL + partial index จะได้ 2 index ทับซ้อน ซ้ำซ้อน + ไม่ได้พฤติกรรมที่ต้องการ)
4. Snapshot ใน order — name, address, price ตอน order
5. Enum status — type-safe
6. Inventory log — audit + reconstruct stock (พร้อม composite index (product_id, created_at))
7. metadata JSONB — flexible attrs (color, size, ...)
8. Composite index — (status, created_at DESC) for common query
9. Email case-insensitive uniqueness — ทางเลือกแทน partial index: ใช้ CITEXT extension (case-insensitive text) บน column email → unique โดยไม่ต้อง expression index LOWER(email)

⚠️ Soft-delete consistency: ตัวอย่างนี้ user มี soft delete แต่ products ไม่มี — ถ้าลบ product ที่ถูกอ้างใน order_items ผ่าน ON DELETE policy จะมีปัญหา. ในระบบจริงควรเลือก strategy เดียว ทั่ว schema (เช่น soft delete entity หลักทั้งหมด หรือ hard delete + history table)

---

12. ⚠️ Anti-patterns

Anti-pattern	แก้
EAV (อ่าน อี-เอ-วี = Entity-Attribute-Value — เก็บทุก attribute เป็นแถว key/value ในตารางเดียว แทนที่จะเป็น column → query ยาก เสียประสิทธิภาพ)	ใช้ JSONB หรือแยก table
Polymorphic FK (อ่าน พอ-ลี-มอร์-ฟิก = "หลายรูป" — 1 col ที่อ้าง multiple table เช่น target_id ที่บางครั้งชี้ users บางครั้งชี้ posts → DB เช็ค FK ไม่ได้)	separate FK column ต่อ target table
Comma-separated value in column	normalize เป็น junction table
God table (50+ columns)	split เป็นหลาย table
ลืม timestamp	created_at + updated_at ทุก table
ไม่มี PK	ทุก table ต้องมี PK
ใช้ id = ทุก table แต่ FK ใช้ users.id ลำบาก	ใช้ id ตาม convention OK
Sensitive data plain text	encrypt at rest หรือ pgcrypto

---

13. Checkpoint

🛠️ Checkpoint 6.1 — Design Schema for Twitter Clone
ออกแบบ schema สำหรับ Twitter:

• user, tweet, follow, like, retweet, reply
• consider: scale (millions of tweets, fan-out)
• ทำ ER diagram
• เขียน CREATE TABLE

🛠️ Checkpoint 6.2 — Normalize
ดูตัวอย่างต่อไปนี้:

orders_flat(order_id, customer_name, customer_email, 
            product1_name, product1_qty, 
            product2_name, product2_qty, ...)

• ระบุปัญหา (ละเมิด NF ไหน)
• Refactor → 3NF
• เขียน migration

🛠️ Checkpoint 6.3 — Soft Delete + Audit
เพิ่ม:

• deleted_at column + partial index
• created_by / updated_by columns
• trigger สำหรับ updated_at

🛠️ Checkpoint 6.4 — State Machine
ทำ table tickets ที่:

• status = ENUM (OPEN, IN_PROGRESS, RESOLVED, CLOSED)
• trigger บังคับ transition (OPEN → IN_PROGRESS, ห้าม OPEN → RESOLVED ตรง ๆ)

---

14. สรุปบท

✅ Normalize ก่อน — 3NF ส่วนใหญ่พอ
✅ 1NF = atomic, 2NF = no partial dep, 3NF = no transitive dep
✅ Denormalize เฉพาะ — measure ก่อน
✅ Surrogate key (SERIAL/UUID) > natural key — 95% case
✅ Naming: snake_case + plural table + singular column
✅ Common patterns: soft delete, audit, event log, state machine, hierarchy
✅ Choose right type — TIMESTAMPTZ, NUMERIC, JSONB, ENUM
✅ Migration ทีละ step + reversible + safe for production
✅ Anti-patterns: EAV, polymorphic FK, god table, comma-separated value

---

← บทที่ 5 | บทที่ 7 → PostgreSQL ลึก