회원 게시판 최종 DDL

앞에서 후보 키, 관계, 정규화, 물리 타입, 파생 데이터를 각각 설계했습니다. 이제 모든 결정을 빈 데이터베이스에서 다시 실행할 수 있는 하나의 회원 게시판 스키마로 묶습니다.

최종 원본은 회원, 프로필, 카테고리, 게시글, 댓글, 태그, 첨부파일을 중심으로 구성합니다. daily_post_stats는 게시글 원본과 같은 트랜잭션에서 갱신하는 운영 통계이며 언제든 posts에서 다시 계산할 수 있어야 합니다.

핵심 조회는 회원별 게시글 목록과 전체 공개 게시글 목록입니다. 둘 다 created_at DESC, id DESC 키셋 순서를 사용해 같은 시각의 행도 빠뜨리지 않습니다.

의존성 순서와 반복 실행

외래 키가 참조하는 부모 테이블은 자식보다 먼저 만들어야 합니다. 삭제할 때는 반대로 자식부터 제거해야 합니다.

생성 순서는 다음과 같습니다.

독립 원본인 members, categories, tags를 만듭니다.
회원과 카테고리를 참조하는 posts를 만듭니다.
회원 프로필과 댓글처럼 한 부모에 종속된 테이블을 만듭니다.
태그와 첨부파일 연결 테이블을 만듭니다.
재생 가능한 운영 통계와 읽기 전용 뷰를 만듭니다.

DDL은 배포 도구가 버전을 기록해 한 번만 적용하는 것이 원칙입니다. 이 문서의 DROP DATABASE는 앞 장에서 만든 모든 실습용 자식 테이블까지 제거하고 연습용 빈 스키마를 다시 만들기 위한 장치입니다. 운영 데이터베이스에서는 절대 실행하지 않습니다.

00_reset_board_lab.sql

DROP DATABASE IF EXISTS board_lab;

CREATE DATABASE board_lab
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE board_lab;

정규 원본 DDL

회원 이메일은 로그인 업무 키이므로 UNIQUE NOT NULL로 보호합니다. 기본 키 id는 이메일이 바뀌어도 게시글과 댓글의 참조를 안정적으로 유지합니다.

01_create_board_schema.sql

CREATE TABLE members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(80) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  CONSTRAINT uq_members_email UNIQUE (email),
  CONSTRAINT ck_members_status
    CHECK (status IN ('ACTIVE', 'SUSPENDED', 'DELETED'))
) ENGINE = InnoDB;

CREATE TABLE categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(40) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(300) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  CONSTRAINT uq_categories_code UNIQUE (code),
  CONSTRAINT uq_categories_name UNIQUE (name)
) ENGINE = InnoDB;

CREATE TABLE tags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(40) NOT NULL,
  name VARCHAR(100) NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  CONSTRAINT uq_tags_code UNIQUE (code),
  CONSTRAINT uq_tags_name UNIQUE (name),
  CONSTRAINT ck_tags_active CHECK (is_active IN (0, 1))
) ENGINE = InnoDB;

CREATE TABLE posts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  author_id BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NULL,
  title VARCHAR(160) NOT NULL,
  content TEXT NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'DRAFT',
  view_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL
    DEFAULT CURRENT_TIMESTAMP(6)
    ON UPDATE CURRENT_TIMESTAMP(6),
  published_at DATETIME(6) NULL,
  version BIGINT UNSIGNED NOT NULL DEFAULT 0,
  request_key VARCHAR(64) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT uq_posts_author_request
    UNIQUE (author_id, request_key),
  CONSTRAINT ck_posts_status
    CHECK (status IN ('DRAFT', 'HIDDEN', 'PUBLISHED', 'DELETED')),
  CONSTRAINT ck_posts_title
    CHECK (CHAR_LENGTH(TRIM(title)) BETWEEN 1 AND 160),
  CONSTRAINT ck_posts_content
    CHECK (CHAR_LENGTH(TRIM(content)) >= 1),
  CONSTRAINT ck_posts_request_key
    CHECK (CHAR_LENGTH(TRIM(request_key)) BETWEEN 1 AND 64),
  CONSTRAINT ck_posts_time_order
    CHECK (
      updated_at >= created_at
      AND (published_at IS NULL OR published_at >= created_at)
    ),
  CONSTRAINT ck_posts_publication_pair
    CHECK (
      (status = 'PUBLISHED' AND published_at IS NOT NULL)
      OR (status = 'DRAFT' AND published_at IS NULL)
      OR status IN ('HIDDEN', 'DELETED')
    ),
  CONSTRAINT fk_posts_author
    FOREIGN KEY (author_id)
    REFERENCES members (id)
    ON DELETE RESTRICT,
  CONSTRAINT fk_posts_category
    FOREIGN KEY (category_id)
    REFERENCES categories (id)
    ON DELETE SET NULL,
  INDEX ix_posts_author_created
    (author_id, created_at DESC, id DESC),
  INDEX ix_posts_status_created
    (status, created_at DESC, id DESC)
) ENGINE = InnoDB;

CREATE TABLE member_profiles (
  member_id BIGINT UNSIGNED NOT NULL,
  bio VARCHAR(500) NULL,
  avatar_url VARCHAR(768) NULL,
  updated_at DATETIME(6) NOT NULL
    DEFAULT CURRENT_TIMESTAMP(6)
    ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (member_id),
  CONSTRAINT fk_member_profiles_member
    FOREIGN KEY (member_id)
    REFERENCES members (id)
    ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE comments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  author_id BIGINT UNSIGNED NOT NULL,
  parent_comment_id BIGINT UNSIGNED NULL,
  content VARCHAR(1000) NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'VISIBLE',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL
    DEFAULT CURRENT_TIMESTAMP(6)
    ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  CONSTRAINT ck_comments_status
    CHECK (status IN ('VISIBLE', 'HIDDEN', 'DELETED')),
  CONSTRAINT ck_comments_parent
    CHECK (parent_comment_id IS NULL OR parent_comment_id <> id),
  CONSTRAINT fk_comments_post
    FOREIGN KEY (post_id)
    REFERENCES posts (id)
    ON DELETE CASCADE,
  CONSTRAINT fk_comments_author
    FOREIGN KEY (author_id)
    REFERENCES members (id)
    ON DELETE RESTRICT,
  CONSTRAINT fk_comments_parent
    FOREIGN KEY (parent_comment_id)
    REFERENCES comments (id)
    ON DELETE SET NULL,
  INDEX ix_comments_post_created
    (post_id, created_at, id),
  INDEX ix_comments_parent
    (parent_comment_id, created_at, id)
) ENGINE = InnoDB;

CREATE TABLE post_tags (
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  source VARCHAR(16) NOT NULL DEFAULT 'MANUAL',
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (post_id, tag_id),
  CONSTRAINT ck_post_tags_source
    CHECK (source IN ('MANUAL', 'AUTO')),
  CONSTRAINT fk_post_tags_post
    FOREIGN KEY (post_id)
    REFERENCES posts (id)
    ON DELETE CASCADE,
  CONSTRAINT fk_post_tags_tag
    FOREIGN KEY (tag_id)
    REFERENCES tags (id)
    ON DELETE CASCADE,
  INDEX ix_post_tags_tag_post (tag_id, post_id)
) ENGINE = InnoDB;

CREATE TABLE attachments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  storage_url VARCHAR(1000) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(127) NOT NULL,
  byte_size BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  CONSTRAINT uq_attachments_storage_url UNIQUE (storage_url),
  CONSTRAINT ck_attachments_byte_size CHECK (byte_size > 0)
) ENGINE = InnoDB;

CREATE TABLE post_attachments (
  post_id BIGINT UNSIGNED NOT NULL,
  attachment_id BIGINT UNSIGNED NOT NULL,
  position SMALLINT UNSIGNED NOT NULL,
  caption VARCHAR(300) NULL,
  PRIMARY KEY (post_id, attachment_id),
  CONSTRAINT uq_post_attachments_position
    UNIQUE (post_id, position),
  CONSTRAINT ck_post_attachments_position
    CHECK (position >= 1),
  CONSTRAINT fk_post_attachments_post
    FOREIGN KEY (post_id)
    REFERENCES posts (id)
    ON DELETE CASCADE,
  CONSTRAINT fk_post_attachments_attachment
    FOREIGN KEY (attachment_id)
    REFERENCES attachments (id)
    ON DELETE RESTRICT
) ENGINE = InnoDB;

CREATE TABLE daily_post_stats (
  member_id BIGINT UNSIGNED NOT NULL,
  stat_date DATE NOT NULL,
  published_count INT UNSIGNED NOT NULL DEFAULT 0,
  total_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
  updated_at DATETIME(6) NOT NULL
    DEFAULT CURRENT_TIMESTAMP(6)
    ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (member_id, stat_date),
  CONSTRAINT ck_daily_post_stats_count
    CHECK (published_count <= 20),
  CONSTRAINT fk_daily_post_stats_member
    FOREIGN KEY (member_id)
    REFERENCES members (id)
    ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE VIEW v_published_posts AS
SELECT
  p.id,
  p.author_id,
  m.name AS author_name,
  p.category_id,
  c.name AS category_name,
  p.title,
  p.content,
  p.view_count,
  p.created_at,
  p.published_at,
  p.version
FROM posts AS p
JOIN members AS m
  ON m.id = p.author_id
LEFT JOIN categories AS c
  ON c.id = p.category_id
WHERE p.status = 'PUBLISHED';

외래 키는 양끝의 존재와 삭제 정책을 보장합니다. comments.parent_comment_id만으로는 부모 댓글이 같은 게시글에 속하는지까지 검사하지 못하므로 답글 등록 트랜잭션에서 부모의 post_id를 함께 확인합니다.

daily_post_stats는 게시글 수 제한과 운영 화면을 위한 파생 행입니다. 게시글 저장과 통계 증가를 같은 트랜잭션에 넣고 정기적으로 posts의 절대 건수와 대조합니다.

시드 데이터와 정상 경로

시드 데이터는 정상 INSERT 순서를 보여 주면서 이후 위반 테스트가 참조할 ID를 고정합니다. 비밀번호 해시는 형식 예시일 뿐 실제 서비스에서는 검증된 비밀번호 해시 라이브러리로 생성해야 합니다.

02_seed_board_data.sql

INSERT INTO members
  (email, password_hash, name)
VALUES
  ('min@example.com', '$2a$12$example.hash', '안동민');

SET @member_id := LAST_INSERT_ID();

INSERT INTO member_profiles
  (member_id, bio, avatar_url)
VALUES
  (@member_id, '데이터베이스와 백엔드를 기록합니다.',
   'https://cdn.example.com/avatars/min.png');

INSERT INTO categories
  (code, name, description)
VALUES
  ('database', '데이터베이스', '관계형 모델과 MySQL 게시글');

SET @category_id := LAST_INSERT_ID();

INSERT INTO tags (code, name)
VALUES ('mysql', 'MySQL'), ('modeling', '모델링');

SET @mysql_tag_id := (
  SELECT id FROM tags WHERE code = 'mysql'
);
SET @modeling_tag_id := (
  SELECT id FROM tags WHERE code = 'modeling'
);

START TRANSACTION;

INSERT INTO posts
  (author_id, category_id, title, content, status,
   published_at, request_key)
VALUES
  (@member_id, @category_id,
   '회원 게시판 최종 모델',
   '정규화된 원본과 관계 제약을 하나의 DDL로 묶습니다.',
   'PUBLISHED', CURRENT_TIMESTAMP(6), 'post-final-001');

SET @post_id := LAST_INSERT_ID();

INSERT INTO daily_post_stats
  (member_id, stat_date, published_count, total_views)
VALUES
  (@member_id, CURRENT_DATE(), 1, 0)
ON DUPLICATE KEY UPDATE
  published_count = published_count + 1;

COMMIT;

INSERT INTO comments
  (post_id, author_id, content)
VALUES
  (@post_id, @member_id, '최종 스키마를 확인했습니다.');

SET @comment_id := LAST_INSERT_ID();

INSERT INTO comments
  (post_id, author_id, parent_comment_id, content)
VALUES
  (@post_id, @member_id, @comment_id, '답글 관계도 정상입니다.');

INSERT INTO post_tags
  (post_id, tag_id, source)
VALUES
  (@post_id, @mysql_tag_id, 'MANUAL'),
  (@post_id, @modeling_tag_id, 'MANUAL');

INSERT INTO attachments
  (storage_url, original_name, mime_type, byte_size)
VALUES
  ('https://cdn.example.com/files/schema.png',
   'schema.png', 'image/png', 245760);

SET @attachment_id := LAST_INSERT_ID();

INSERT INTO post_attachments
  (post_id, attachment_id, position, caption)
VALUES
  (@post_id, @attachment_id, 1, '최종 ERD');

제약 위반으로 설계 의도 확인

정상 시드가 들어간 뒤에는 각 제약이 막아야 할 반례를 하나씩 실행합니다. 오류 번호보다 어떤 이름의 제약이 어떤 업무 문장을 지켰는지 확인합니다.

03_expected_failures.sql

-- 같은 이메일은 두 회원에게 사용할 수 없다.
INSERT INTO members
  (email, password_hash, name)
VALUES
  ('min@example.com', '$2a$12$duplicate.hash', '중복 회원');
-- ERROR 1062: uq_members_email

-- 존재하지 않는 회원은 게시글 작성자가 될 수 없다.
INSERT INTO posts
  (author_id, title, content, status, request_key)
VALUES
  (999999, '고아 게시글', '저장되면 안 됩니다.', 'DRAFT', 'orphan-001');
-- ERROR 1452: fk_posts_author

-- 같은 작성 요청을 재시도해도 게시글이 중복되지 않는다.
INSERT INTO posts
  (author_id, title, content, status, request_key)
VALUES
  (@member_id, '중복 요청', '저장되면 안 됩니다.', 'DRAFT', 'post-final-001');
-- ERROR 1062: uq_posts_author_request

-- 공개 시각 없는 공개 게시글은 상태 규칙을 위반한다.
INSERT INTO posts
  (author_id, title, content, status, published_at, request_key)
VALUES
  (@member_id, '잘못된 공개 상태', '저장되면 안 됩니다.',
   'PUBLISHED', NULL, 'invalid-status-001');
-- ERROR 3819: ck_posts_publication_pair

-- 같은 태그 연결과 같은 첨부 순서는 중복될 수 없다.
INSERT INTO post_tags (post_id, tag_id)
VALUES (@post_id, @mysql_tag_id);
-- ERROR 1062: post_tags.PRIMARY

INSERT INTO post_attachments
  (post_id, attachment_id, position)
VALUES
  (@post_id, @attachment_id, 1);
-- ERROR 1062: post_attachments.PRIMARY 또는 uq_post_attachments_position

실패 예제는 운영 마이그레이션에 그대로 넣지 않습니다. 별도 검증 스크립트나 수동 인수 절차로 실행하고 예상한 제약 이름을 기록합니다.

0행 무결성 대사

제약이 활성화되어 있어도 과거 데이터 이관과 제약 비활성 이력을 확인할 진단 쿼리가 필요합니다. 다음 쿼리는 정상 상태에서 모두 0행을 반환해야 합니다.

04_integrity_audit.sql

SELECT p.id AS orphan_post_id
FROM posts AS p
LEFT JOIN members AS m
  ON m.id = p.author_id
WHERE m.id IS NULL;

SELECT c.id AS orphan_comment_id
FROM comments AS c
LEFT JOIN posts AS p
  ON p.id = c.post_id
LEFT JOIN members AS m
  ON m.id = c.author_id
WHERE p.id IS NULL OR m.id IS NULL;

SELECT c.id AS cross_post_reply_id
FROM comments AS c
JOIN comments AS parent
  ON parent.id = c.parent_comment_id
WHERE c.post_id <> parent.post_id;

SELECT pt.post_id, pt.tag_id, COUNT(*) AS duplicate_count
FROM post_tags AS pt
GROUP BY pt.post_id, pt.tag_id
HAVING COUNT(*) > 1;

SELECT
  s.member_id,
  s.stat_date,
  s.published_count,
  COUNT(p.id) AS source_count,
  s.total_views,
  COALESCE(SUM(p.view_count), 0) AS source_total_views
FROM daily_post_stats AS s
LEFT JOIN posts AS p
  ON p.author_id = s.member_id
 AND p.status = 'PUBLISHED'
 AND p.published_at >= s.stat_date
 AND p.published_at < s.stat_date + INTERVAL 1 DAY
GROUP BY
  s.member_id,
  s.stat_date,
  s.published_count,
  s.total_views
HAVING s.published_count <> COUNT(p.id)
    OR s.total_views <> COALESCE(SUM(p.view_count), 0);

세 번째 쿼리는 단순 자기 참조 FK가 강제하지 못한 “부모 답글과 같은 게시글” 규칙을 검사합니다. 마지막 쿼리는 파생 통계가 정규 원본과 어긋난 회원·날짜만 보여 줍니다.

키셋 페이지 조회 계획

페이지 번호가 커질수록 OFFSET은 앞 행을 버리는 비용이 커집니다. 마지막으로 본 created_at과 id를 커서로 넘기면 다음 페이지의 시작점을 인덱스 범위로 찾을 수 있습니다.

05_keyset_pagination.sql

SET @cursor_created_at := '2026-07-15 23:59:59.999999';
SET @cursor_id := 18446744073709551615;

EXPLAIN ANALYZE
SELECT
  p.id,
  p.title,
  p.status,
  p.view_count,
  p.created_at
FROM posts AS p
WHERE p.author_id = @member_id
  AND (
    p.created_at < @cursor_created_at
    OR (p.created_at = @cursor_created_at AND p.id < @cursor_id)
  )
ORDER BY p.created_at DESC, p.id DESC
LIMIT 20;

ix_posts_author_created (author_id, created_at DESC, id DESC)는 작성자 조건과 커서 정렬을 한 경로로 지원합니다. 같은 created_at을 가진 여러 행이 있어도 id가 두 번째 정렬 키이므로 누락과 중복을 막습니다.

전체 공개 목록은 ix_posts_status_created를 사용합니다. 실제 선택 인덱스와 읽은 행 수는 충분한 데이터에서 EXPLAIN ANALYZE로 확인합니다.

배포와 인수 기준

빈 스키마에서 성공했다고 운영 변경까지 안전한 것은 아닙니다. 대형 테이블의 인덱스 추가, NOT NULL 강화, 타입 변경은 잠금과 복제 지연을 만들 수 있습니다.

배포 전에는 다음 항목을 확인합니다.

적용 순서와 롤포워드 스크립트가 버전 관리에 들어 있는지 확인합니다.
새 필수 컬럼은 NULL 허용 추가, 백필, 0행 누락 검사, 제약 강화 순서로 적용합니다.
외래 키 부모·자식 타입과 문자 정렬 규칙이 같은지 확인합니다.
새 인덱스의 대표 쿼리 계획과 쓰기 비용을 함께 비교합니다.
파생 통계는 재생성 쿼리와 불일치 대사를 먼저 준비합니다.
개인정보 삭제와 게시글 보존 정책이 CASCADE·RESTRICT 선택과 일치하는지 확인합니다.

연습 문제

댓글을 소프트 삭제할 때 대댓글 문맥은 남기되 작성자 개인정보는 화면에 노출하지 않으려 합니다. comments.status, content, 회원 탈퇴 상태, 조회 쿼리의 책임을 나누어 설계하세요.

해설과 예시 답안

댓글 행과 parent_comment_id는 유지해 답글 트리를 보존합니다. 삭제 시 status='DELETED'로 바꾸고 화면에는 고정 문구를 렌더링합니다. 작성자 FK를 지우지 않는다면 탈퇴 회원의 이름을 노출하지 않도록 회원 상태와 댓글 상태를 함께 검사합니다. 법적 삭제 요구가 있으면 별도 익명 회원으로 참조를 옮기는 마이그레이션과 감사 근거가 필요합니다.

SELECT
  c.id,
  c.parent_comment_id,
  CASE
    WHEN c.status = 'DELETED' THEN '삭제된 댓글입니다.'
    ELSE c.content
  END AS visible_content,
  CASE
    WHEN c.status = 'DELETED' OR m.status = 'DELETED'
      THEN '탈퇴한 회원'
    ELSE m.name
  END AS visible_author
FROM comments AS c
JOIN members AS m
  ON m.id = c.author_id
WHERE c.post_id = @post_id
ORDER BY c.created_at, c.id;

핵심 정리

최종 DDL은 키, 관계, 정규화, 타입, 인덱스, 파생 데이터의 결정을 한 실행 순서로 묶습니다.
회원 이메일과 게시글 요청 키는 업무 유일성으로, id는 안정적인 참조 식별자로 분리합니다.
댓글, 태그, 첨부파일은 외래 키와 복합 유일성으로 회원 가입·게시판의 실제 관계를 표현합니다.
키셋 정렬은 created_at DESC, id DESC 두 값을 함께 사용합니다.
파생 통계는 정규 원본, 기준 날짜, 불일치 대사, 재생성 절차를 가져야 합니다.
성공 경로뿐 아니라 예상 실패와 0행 진단까지 통과해야 스키마 인수가 끝납니다.

이 페이지의 목차