ERD와 데이터 모델 검증

DROP TABLE IF EXISTS post_comments_wide_bad;
CREATE TABLE post_comments_wide_bad (
  post_id BIGINT UNSIGNED NOT NULL,
  author_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(80) NOT NULL,
  comment_1_author_id BIGINT UNSIGNED NULL,
  comment_1_content VARCHAR(1000) NULL,
  comment_2_author_id BIGINT UNSIGNED NULL,
  comment_2_content VARCHAR(1000) NULL,
  PRIMARY KEY (post_id)
) ENGINE = InnoDB;

INSERT INTO post_comments_wide_bad (
  post_id, author_id, title,
  comment_2_author_id, comment_2_content
)
VALUES (1, 1, '반복 열의 문제', 2, '첫 슬롯 없이 두 번째 댓글');

SELECT post_id, comment_1_content, comment_2_content
FROM post_comments_wide_bad;

post_id | comment_1_content | comment_2_content
1       | NULL              | 첫 슬롯 없이 두 번째 댓글

DROP TABLE IF EXISTS concept_comment_reactions;
DROP TABLE IF EXISTS concept_post_attachments;
DROP TABLE IF EXISTS concept_post_tags;
DROP TABLE IF EXISTS concept_tags;
DROP TABLE IF EXISTS concept_comments;

CREATE TABLE concept_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),
  PRIMARY KEY (id),
  CONSTRAINT fk_concept_comments_post
    FOREIGN KEY (post_id) REFERENCES concept_posts (post_id),
  CONSTRAINT fk_concept_comments_author
    FOREIGN KEY (author_id) REFERENCES concept_members (member_id),
  CONSTRAINT fk_concept_comments_parent
    FOREIGN KEY (parent_comment_id) REFERENCES concept_comments (id),
  CONSTRAINT chk_concept_comments_content
    CHECK (CHAR_LENGTH(content) BETWEEN 1 AND 1000),
  CONSTRAINT chk_concept_comments_status
    CHECK (status IN ('VISIBLE', 'HIDDEN', 'DELETED'))
) ENGINE = InnoDB;

CREATE TABLE concept_tags (
  tag_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  PRIMARY KEY (tag_id),
  UNIQUE KEY uq_concept_tags_name (name)
) ENGINE = InnoDB;

CREATE TABLE concept_post_tags (
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  attached_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES concept_posts (post_id),
  FOREIGN KEY (tag_id) REFERENCES concept_tags (tag_id)
) ENGINE = InnoDB;

CREATE TABLE concept_post_attachments (
  post_id BIGINT UNSIGNED NOT NULL,
  attachment_id BIGINT UNSIGNED NOT NULL,
  display_order SMALLINT UNSIGNED NOT NULL,
  caption VARCHAR(200) NULL,
  PRIMARY KEY (post_id, attachment_id),
  UNIQUE (post_id, display_order),
  FOREIGN KEY (post_id) REFERENCES concept_posts (post_id),
  FOREIGN KEY (attachment_id)
    REFERENCES concept_attachments (attachment_id)
) ENGINE = InnoDB;

SET @concept_member_id := (
  SELECT member_id FROM concept_members WHERE email = '[email protected]'
);
SET @concept_post_id := (SELECT MIN(post_id) FROM concept_posts);
SET @concept_attachment_id := (
  SELECT MIN(attachment_id) FROM concept_attachments
);

INSERT INTO concept_comments
  (post_id, author_id, content, status, created_at)
VALUES
  (@concept_post_id, @concept_member_id, '관계 모델이 이해됩니다.',
   'VISIBLE', '2026-07-14 13:00:00');

SET @concept_parent_comment_id := LAST_INSERT_ID();

INSERT INTO concept_comments
  (post_id, author_id, parent_comment_id, content, status, created_at)
VALUES
  (@concept_post_id, @concept_member_id, @concept_parent_comment_id,
   '대댓글도 같은 게시글에 둡니다.', 'VISIBLE',
   '2026-07-14 13:10:00');

INSERT INTO concept_tags (name) VALUES ('mysql');
SET @concept_tag_id := LAST_INSERT_ID();

INSERT INTO concept_post_tags (post_id, tag_id, attached_at)
VALUES (@concept_post_id, @concept_tag_id, '2026-07-14 13:20:00');

INSERT INTO concept_post_attachments
  (post_id, attachment_id, display_order, caption)
VALUES
  (@concept_post_id, @concept_attachment_id, 1, '인덱스 참고 자료');

SELECT c.id
FROM concept_comments AS c
LEFT JOIN concept_members AS m
  ON m.member_id = c.author_id
LEFT JOIN concept_posts AS p
  ON p.post_id = c.post_id
WHERE m.member_id IS NULL OR p.post_id IS NULL;

SELECT child.id, child.post_id, parent.post_id AS parent_post_id
FROM concept_comments AS child
JOIN concept_comments AS parent
  ON parent.id = child.parent_comment_id
WHERE child.post_id <> parent.post_id;

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

SELECT post_id, display_order, COUNT(*) AS duplicate_count
FROM concept_post_attachments
GROUP BY post_id, display_order
HAVING COUNT(*) > 1;