① 요구·접근 패턴 분석
    무엇을 저장하나 · 어떻게 읽나(단건/목록/집계) · 읽기:쓰기 비율은?
   │
   ▼
② 정규화 수준 결정   (기본은 3NF)
    각 사실을 한 곳에만 → 갱신 이상 차단이 출발점
   │
   ▼
③ 관계·인덱스 설계
    1:N·N:M을 FK/중간 테이블로 · N쪽 FK 컬럼 인덱스 · 조회 패턴에 맞는 인덱스
   │
   ▼
④ 비정규화 트레이드오프 판단   ← 측정된 병목이 있을 때만
    조인 폭증(과정규화)  vs  갱신 이상·동기화 책임(과비정규화)
   │
   ▼
⑤ 검증
    EXPLAIN으로 실제 실행계획 확인 · 이상 현상 재현 테스트 · 무결성 제약 점검

CREATE TABLE categories (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    parent_id  INT REFERENCES categories(id),
    CONSTRAINT chk_not_self CHECK (id != parent_id)
);

INSERT INTO categories (id, name, parent_id) VALUES
    (1, '전자기기',     NULL),
    (2, '컴퓨터',       1),
    (3, '노트북',       2),
    (4, '게이밍 노트북', 3),
    (5, '스마트폰',     1);

실행 완료 또는 조회 결과가 표시됩니다.

SELECT id, name FROM categories WHERE parent_id = 1;

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS depth,
           ARRAY[id] AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.depth + 1,
           ct.path || c.id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    REPEAT('  ', depth) || name AS indented_name,
    depth,
    path
FROM category_tree
ORDER BY path;

CREATE TABLE categories_path (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    path     VARCHAR(500) NOT NULL
);

INSERT INTO categories_path VALUES
    (1, '전자기기',     '/1/'),
    (2, '컴퓨터',       '/1/2/'),
    (3, '노트북',       '/1/2/3/'),
    (4, '게이밍 노트북', '/1/2/3/4/');

SELECT * FROM categories_path WHERE path LIKE '/1/2/3/%';

SELECT * FROM categories_path
WHERE '/1/2/3/4/' LIKE path || '%';

CREATE INDEX idx_cat_path ON categories_path (path text_pattern_ops);

CREATE TABLE categories_nested (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100),
    lft  INT NOT NULL,
    rgt  INT NOT NULL
);

SELECT c.* FROM categories_nested AS c
WHERE c.lft BETWEEN 2 AND 7;

SELECT p.* FROM categories_nested AS p
WHERE p.lft < 3 AND p.rgt > 6;

CREATE TABLE articles (
    id         SERIAL PRIMARY KEY,
    title      VARCHAR(200) NOT NULL,
    content    TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tags (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(50) UNIQUE NOT NULL,
    slug       VARCHAR(50) UNIQUE NOT NULL,
    tag_count  INT DEFAULT 0
);

CREATE TABLE article_tags (
    article_id INT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    tag_id     INT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (article_id, tag_id)
);

CREATE INDEX idx_article_tags_tag_id ON article_tags (tag_id);

SELECT t.name, t.slug
FROM tags t
JOIN article_tags at ON t.id = at.tag_id
WHERE at.article_id = 42;

SELECT a.id, a.title, a.created_at
FROM articles a
JOIN article_tags at ON a.id = at.article_id
JOIN tags t ON at.tag_id = t.id
WHERE t.slug = 'postgresql'
ORDER BY a.created_at DESC
LIMIT 20;

SELECT a.id, a.title
FROM articles a
JOIN article_tags at ON a.id = at.article_id
JOIN tags t ON at.tag_id = t.id
WHERE t.slug IN ('postgresql', 'performance', 'indexing')
GROUP BY a.id, a.title
HAVING COUNT(DISTINCT t.slug) = 3;

SELECT t.name, COUNT(at.article_id) AS cnt
FROM tags t
LEFT JOIN article_tags at ON t.id = at.tag_id
GROUP BY t.id, t.name
ORDER BY cnt DESC;

UPDATE tags SET tag_count = tag_count + 1 WHERE id = :tag_id;

SELECT name, slug, tag_count FROM tags
WHERE tag_count > 0
ORDER BY tag_count DESC
LIMIT 30;

CREATE TABLE articles (
    id         SERIAL PRIMARY KEY,
    title      VARCHAR(200) NOT NULL,
    content    TEXT,
    author_id  INT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ NULL
);

UPDATE articles SET deleted_at = NOW() WHERE id = 42;

UPDATE articles SET deleted_at = NULL WHERE id = 42;

DELETE FROM articles WHERE deleted_at < NOW() - INTERVAL '90 days';

SELECT * FROM articles WHERE deleted_at IS NULL ORDER BY created_at DESC;

SELECT * FROM articles WHERE deleted_at IS NOT NULL;

CREATE INDEX idx_articles_active ON articles (author_id, created_at DESC)
WHERE deleted_at IS NULL;

SELECT * FROM articles
WHERE author_id = 42 AND deleted_at IS NULL
ORDER BY created_at DESC;

CREATE TABLE comments (
    id                SERIAL PRIMARY KEY,
    body              TEXT NOT NULL,
    author_id         INT NOT NULL REFERENCES users(id),
    commentable_type  VARCHAR(50) NOT NULL,
    commentable_id    INT NOT NULL,
    created_at        TIMESTAMPTZ DEFAULT NOW(),
    deleted_at        TIMESTAMPTZ NULL
);

CREATE INDEX idx_comments_polymorphic
ON comments (commentable_type, commentable_id)
WHERE deleted_at IS NULL;

SELECT * FROM comments
WHERE commentable_type = 'Article'
  AND commentable_id = 42
  AND deleted_at IS NULL;

SELECT c.*, c.commentable_type, c.commentable_id
FROM comments c
WHERE c.author_id = 7 AND c.deleted_at IS NULL;

CREATE TABLE article_comments (
    id         SERIAL PRIMARY KEY,
    article_id INT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    body       TEXT NOT NULL,
    author_id  INT NOT NULL REFERENCES users(id)
);

CREATE TABLE product_comments (
    id         SERIAL PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    body       TEXT NOT NULL,
    author_id  INT NOT NULL REFERENCES users(id)
);