일대일과 다대다 관계

회원 게시판의 회원에게 선택적인 프로필을 붙이고, 각 게시글에는 여러 태그를 달고 싶습니다.

프로필은 회원과 일대일, 게시글과 태그는 다대다 관계입니다.

둘 다 선 하나를 그리는 일처럼 보이지만 관계형 테이블에서는 전혀 다른 제약이 필요합니다.

일대일은 외래 키에 유일성을 더해 “최대 하나”를 만들고, 다대다는 관계 자체를 한 행으로 승격해 두 개의 일대다로 풉니다.

중요한 것은 기호를 외우는 것이 아니라 관계의 행을 어디에 저장하고 어떤 중복을 금지할지 결정하는 일입니다.

일대일 관계의 유일성

회원 한 명이 프로필을 최대 하나만 가진다고 해 봅시다.

member_profiles.member_id가 회원을 참조하는 외래 키이기만 하면, 같은 회원을 가리키는 프로필 여러 행을 저장할 수 있습니다.

외래 키는 “존재하는 회원인가?”를 검사할 뿐 “몇 번 참조했는가?”를 제한하지 않기 때문입니다.

일대일을 만들려면 외래 키 컬럼이 UNIQUE이거나 기본 키여야 합니다.

회원 게시판은 보조 테이블의 외래 키를 그대로 기본 키로 사용하는 공유 기본 키 방식을 선택합니다.

USE board_lab;

SET @member_id := (
  SELECT id
  FROM members
  ORDER BY id
  LIMIT 1
);

DROP TABLE IF EXISTS member_profiles;

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)
) ENGINE = InnoDB;

INSERT INTO member_profiles
  (member_id, bio, avatar_url)
VALUES
  (@member_id, '게시판 운영자입니다.', 'https://cdn.example.com/avatar/min.png');

이 구조에는 두 제약이 동시에 작동합니다.

• 외래 키는 존재하지 않는 회원의 프로필을 막습니다.
• 기본 키는 같은 회원의 두 번째 프로필을 막습니다.

INSERT INTO member_profiles
  (member_id, bio, avatar_url)
VALUES
  (@member_id, '중복 프로필', NULL);

ERROR 1062 (23000): Duplicate entry '<member_id>'
for key 'member_profiles.PRIMARY'

이 모델에서 프로필은 선택 참여입니다.

회원 행만 있고 프로필이 없어도 됩니다.

하지만 프로필이 존재한다면 반드시 회원 하나를 가리킵니다.

모든 회원에게 프로필이 반드시 있어야 한다는 부모 쪽 최소 참여는 이 외래 키만으로 강제되지 않습니다.

외래 키는 어느 쪽에 둘까

일대일은 양쪽 모두 최대 하나이므로 일대다처럼 ‘다’ 쪽이 분명하지 않습니다.

보통 수명과 의존성을 기준으로 고릅니다.

• 프로필이 회원 없이 존재할 수 없다면 보조 테이블인 member_profiles이 회원을 참조합니다.
• 선택 정보가 분리되어 있으면 members에 NULL 허용 프로필 ID를 추가하지 않아도 됩니다.
• 나중에 프로필 이력이나 여러 공개 프로필로 확장할 때 주 테이블을 덜 바꿀 수 있습니다.

반대로 보조 행이 항상 먼저 존재하고 주 테이블이 그 행을 반드시 가져야 하거나, 특정 조회 경로에서 직접 식별자가 꼭 필요한 경우에는 주 테이블에 외래 키를 둘 수도 있습니다.

다만 “조인이 하나 줄 것 같다”는 추측만으로 생명주기 방향을 뒤집기보다 실제 쿼리 계획과 변경 가능성을 확인해야 합니다.

일대일 분리는 개인정보 접근 제어, 매우 큰 선택 컬럼, 서로 다른 변경 빈도처럼 분명한 이유가 있을 때 유용합니다.

단지 파일을 나누듯 테이블을 잘게 쪼개면 조인과 무결성 관리만 늘어날 수 있습니다.

다대다 관계의 연결 테이블

한 게시글에는 mysql, modeling 같은 여러 태그가 달리고, 한 태그는 여러 게시글에 재사용됩니다.

게시글에 tag_ids = '1,2,5'를 저장하면 컬럼 하나에 여러 값이 들어갑니다.

JSON 배열을 쓰더라도 배열 요소 각각을 tags.id 외래 키로 선언할 수 없으므로 관계의 참조 무결성을 얻지 못합니다.

반대로 태그에 post_ids 목록을 넣어도 같은 문제가 방향만 바뀝니다.

다대다는 어느 한쪽 행에 상대 목록을 욱여넣는 방식으로 해결되지 않습니다.

관계를 저장할 별도 행을 만들면 문제가 단순해집니다.

post_tags 한 행은 게시글 하나와 태그 하나만 가리킵니다.

그 결과 posts 1:N post_tags와 tags 1:N post_tags라는 두 관계가 됩니다.

DROP TABLE IF EXISTS post_tags;
DROP TABLE IF EXISTS tags;

CREATE TABLE tags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tags_name (name)
) ENGINE = InnoDB;

CREATE TABLE post_tags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  source VARCHAR(20) NOT NULL DEFAULT 'MANUAL',
  attached_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_post_tags_pair (post_id, tag_id),
  KEY ix_post_tags_tag_post (tag_id, post_id),
  CONSTRAINT ck_post_tags_source
    CHECK (source IN ('MANUAL', 'AUTO')),
  CONSTRAINT fk_post_tags_post
    FOREIGN KEY (post_id)
    REFERENCES posts (id),
  CONSTRAINT fk_post_tags_tag
    FOREIGN KEY (tag_id)
    REFERENCES tags (id)
) ENGINE = InnoDB;

post_tags.id는 연결 행의 대표 식별자입니다.

(post_id, tag_id)는 “한 게시글에 같은 태그를 한 번만 붙인다”는 복합 후보 키이므로 UNIQUE로 남겼습니다.

연결 행을 다른 테이블이 참조하지 않는 단순한 모델이라면 두 외래 키를 복합 기본 키로 삼는 선택도 가능합니다.

source와 attached_at은 게시글이나 태그 단독의 속성이 아닙니다.

그 태그가 그 게시글에 붙은 관계에서만 의미가 있습니다.

연결 테이블이 단순한 기술적 다리가 아니라 업무 데이터를 담는 연관 엔티티가 되는 지점입니다.

연결 제약 검증

태그를 만들고 첫 번째 게시글에 연결해 봅시다.

INSERT INTO tags (name)
VALUES ('mysql'), ('logical-modeling'), ('key-design');

SET @first_post_id := (
  SELECT MIN(id)
  FROM posts
  WHERE author_id = @member_id
);

INSERT INTO post_tags (post_id, tag_id, source)
SELECT @first_post_id, id, 'MANUAL'
FROM tags
WHERE name IN ('mysql', 'key-design');

SELECT
  s.id AS post_id,
  s.title,
  t.name AS tag_name,
  st.source,
  st.attached_at
FROM posts AS s
JOIN post_tags AS st
  ON st.post_id = s.id
JOIN tags AS t
  ON t.id = st.tag_id
WHERE s.id = @first_post_id
ORDER BY t.name;

한 게시글이 두 연결 행을 통해 두 태그와 이어집니다.

이번에는 같은 연결을 다시 넣어 봅시다.

INSERT INTO post_tags (post_id, tag_id, source)
SELECT @first_post_id, id, 'AUTO'
FROM tags
WHERE name = 'mysql';

ERROR 1062 (23000): Duplicate entry '<post_id>-<tag_id>'
for key 'post_tags.uq_post_tags_pair'

대리 키 post_tags.id만 있었다면 중복 연결도 서로 다른 행으로 저장됐을 것입니다.

복합 대체 키가 관계의 업무 유일성을 지켰습니다.

존재하지 않는 태그 ID를 넣으면 이번에는 외래 키 오류가 발생합니다.

INSERT INTO post_tags (post_id, tag_id, source)
VALUES (@first_post_id, 999999, 'MANUAL');

ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (... fk_post_tags_tag ...)

유일성 제약은 “같은 관계가 몇 번 존재할 수 있는가”를, 외래 키는 “관계 양끝이 실제로 존재하는가”를 각각 보장합니다.

관계 속성과 양방향 탐색

특정 태그가 붙은 모든 게시글을 찾을 때는 태그에서 연결 테이블을 거쳐 게시글으로 이동합니다.

SELECT
  t.name AS tag_name,
  s.id AS post_id,
  s.title,
  s.created_at
FROM tags AS t
JOIN post_tags AS st
  ON st.tag_id = t.id
JOIN posts AS s
  ON s.id = st.post_id
WHERE t.name = 'mysql'
ORDER BY s.created_at DESC;

외래 키는 post_tags에만 있지만 어느 방향으로도 조회할 수 있습니다.

ix_post_tags_tag_post (tag_id, post_id)는 태그에서 시작하는 경로를 돕고, UNIQUE (post_id, tag_id)는 게시글에서 시작하는 경로의 왼쪽 접두사도 제공합니다.

실제 인덱스 선택과 읽은 행 수는 데이터가 충분히 쌓인 뒤 EXPLAIN ANALYZE로 확인해야 합니다.

관계 속성이 늘어나면 이름도 다시 봐야 합니다.

post_tags에 검수 상태, 추천 근거, 제거 시각이 추가된다면 “태그 부착”이라는 독립 개념으로 다뤄야 할 수 있습니다.

이름은 현재 구현이 아니라 행이 표현하는 업무 사실을 드러내야 합니다.

관계 유형 선택 순서

카디널리티는 현재 데이터 개수가 아니라 허용 가능한 최대 개수로 판단합니다.

지금 모든 회원이 프로필 하나를 갖고 있어도 프로필이 선택 사항이면 최소값은 0입니다.

지금 태그 하나만 붙어 있어도 앞으로 여러 태그를 허용한다면 최대값은 N입니다.

1. 양쪽에서 최소 개수와 최대 개수를 각각 적습니다.
2. 외래 키가 있는 행 하나가 상대 행 몇 개를 가리켜야 하는지 봅니다.
3. 일대일이면 FK의 유일성까지 선언합니다.
4. 일대다면 외래 키를 다 쪽에 둡니다.
5. 다대다면 관계를 연결 행으로 바꾸고 두 개의 일대다로 풉니다.
6. 관계 자체에 발생하는 시각, 상태, 순서, 수량 같은 속성이 없는지 다시 묻습니다.

일대일이 시간이 지나 일대다로 바뀔 가능성이 크다면 처음부터 “현재 활성 행은 하나”인 일대다 모델이 더 맞을 수 있습니다.

예를 들어 프로필 수정 이력을 모두 보존해야 한다면 member_profiles 한 행을 덮어쓰기보다 member_profiles_revision 여러 행과 현재 버전 포인터를 설계해야 합니다.

카디널리티는 화면 모양이 아니라 데이터의 수명 규칙에서 나옵니다.

연습 문제

게시글에는 여러 첨부파일이 연결되고, 같은 첨부파일은 여러 게시글에서 재사용됩니다.

연결될 때마다 게시글 안의 표시 순서 position과 개인 메모 memo를 기록해야 합니다.

1. 두 엔티티 사이의 카디널리티를 판단하세요.
2. 관계 속성을 고르세요.
3. 한 게시글에서 같은 첨부파일을 두 번 연결하지 못하게 하되, 표시 순서도 겹치지 않게 DDL을 작성하세요.

CREATE TABLE attachments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  canonical_url VARCHAR(768) NOT NULL,
  title VARCHAR(200) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_attachments_url (canonical_url)
) ENGINE = InnoDB;

CREATE TABLE post_attachments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  attachment_id BIGINT UNSIGNED NOT NULL,
  position SMALLINT UNSIGNED NOT NULL,
  memo VARCHAR(300) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_post_attachments_pair (post_id, attachment_id),
  UNIQUE KEY uk_post_attachments_position (post_id, position),
  CONSTRAINT ck_post_attachments_position CHECK (position >= 1),
  CONSTRAINT fk_post_attachments_post
    FOREIGN KEY (post_id) REFERENCES posts (id),
  CONSTRAINT fk_post_attachments_attachment
    FOREIGN KEY (attachment_id) REFERENCES attachments (id)
) ENGINE = InnoDB;

핵심 정리

• 일대일은 외래 키에 UNIQUE 또는 기본 키 제약이 더해져야 합니다.
• 의존적인 보조 테이블이 주 테이블을 참조하면 선택 정보와 수명 기준을 분리하기 쉽습니다.
• 다대다는 어느 한쪽에 ID 목록을 넣지 않고 연결 테이블로 두 개의 일대다 관계로 풉니다.
• 연결 테이블의 복합 유일성은 중복 관계를 막고, 외래 키는 양끝의 존재를 보장합니다.
• 시각, 상태, 순서처럼 관계에서 생긴 속성은 연결 테이블에 저장합니다.

이로써 회원 게시판의 회원, 외부 계정, 댓글, 카테고리, 게시글, 프로필, 태그가 안정적인 키와 관계로 연결되었습니다.

다음 장부터는 이 논리 모델을 정규화하고 MySQL 8.4의 실제 타입·인덱스에 맞춘 물리 모델로 다듬을 수 있습니다.