회원 게시판 최종 DDL

DROP DATABASE IF EXISTS board_lab;

CREATE DATABASE board_lab
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE board_lab;
SET SESSION time_zone = '+00:00';

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 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(768) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin 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 ROW_FORMAT = DYNAMIC;

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';

INSERT INTO members
  (email, password_hash, name)
VALUES
  ('[email protected]', '$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,
   created_at, published_at, request_key)
VALUES
  (@member_id, @category_id,
   '회원 게시판 최종 모델',
   '정규화된 원본과 관계 제약을 하나의 DDL로 묶습니다.',
   'PUBLISHED', '2026-07-15 09:00:00', '2026-07-15 09:00:00',
   '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, '2026-07-15', 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');

SELECT
  (SELECT COUNT(*) FROM members) AS members,
  (SELECT COUNT(*) FROM posts) AS posts,
  (SELECT COUNT(*) FROM comments) AS comments,
  (SELECT COUNT(*) FROM post_tags) AS post_tags,
  (SELECT COUNT(*) FROM post_attachments) AS post_attachments,
  (SELECT published_count FROM daily_post_stats) AS stat_count;

SELECT id, author_name, category_name, title
FROM v_published_posts;

members | posts | comments | post_tags | post_attachments | stat_count
1       | 1     | 2        | 2         | 1                | 1

id | author_name | category_name | title
1  | 안동민      | 데이터베이스  | 회원 게시판 최종 모델

-- 같은 이메일은 두 회원에게 사용할 수 없다.
INSERT INTO members
  (email, password_hash, name)
VALUES
  ('[email protected]', '$2a$12$duplicate.hash', '중복 회원');

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

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

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

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

INSERT INTO post_attachments
  (post_id, attachment_id, position)
VALUES
  (@post_id, @attachment_id, 1);

ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'members.uq_members_email'
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`board_lab`.`posts`, CONSTRAINT `fk_posts_author` ...)
ERROR 1062 (23000): Duplicate entry '1-post-final-001' for key 'posts.uq_posts_author_request'
ERROR 3819 (HY000): Check constraint 'ck_posts_publication_pair' is violated.
ERROR 1062 (23000): Duplicate entry '1-1' for key 'post_tags.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1-1' for key 'post_attachments.PRIMARY'

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);

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

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;

id | title                 | status    | view_count | created_at
1  | 회원 게시판 최종 모델 | PUBLISHED | 0          | 2026-07-15 09:00:00.000000

START TRANSACTION;

UPDATE daily_post_stats
SET published_count = 20
WHERE member_id = @member_id AND stat_date = '2026-07-15';

INSERT INTO posts
  (author_id, category_id, title, content, status,
   created_at, published_at, request_key)
VALUES
  (@member_id, @category_id, '스물한 번째 글', '상한을 확인하는 글입니다.',
   'PUBLISHED', '2026-07-15 10:00:00', '2026-07-15 10:00:00',
   'post-final-021');

INSERT INTO daily_post_stats
  (member_id, stat_date, published_count, total_views)
VALUES
  (@member_id, '2026-07-15', 1, 0)
ON DUPLICATE KEY UPDATE
  published_count = published_count + 1;