공유 기본 키와 연결 테이블

식별 관계가 언제나 나쁜 것은 아닙니다. member_profiles가 회원 없이 존재할 수 없고 회원마다 많아야 하나라면, member_id를 프로필의 기본 키이자 외래 키로 쓰는 공유 기본 키가 선택적인 1:1을 간결하게 나타냅니다. post_tags의 연결도 두 부모의 조합 자체가 관계의 정체성일 수 있습니다.

관계마다 수명과 바깥의 참조가 다르므로 키의 전략도 다르게 고릅니다. 이 글은 10장 4편의 프로필과 태그 모델을, 식별 관계의 관점에서 다시 만듭니다.

유일성이 없는 일대일

아래 예제는 members에 행이 하나 이상, posts에 행이 두 개 이상 있다고 전제합니다. 프로필에 profile_id라는 대리 기본 키만 두고 member_id를 일반 외래 키로 쓰면, 같은 회원의 프로필 여러 행을 데이터베이스가 받아들입니다.

DROP TABLE IF EXISTS member_profiles_bad;

SET @profile_member_id := (SELECT MIN(id) FROM members);

CREATE TABLE member_profiles_bad (
  profile_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  member_id BIGINT UNSIGNED NOT NULL,
  bio VARCHAR(500) NULL,
  PRIMARY KEY (profile_id),
  FOREIGN KEY (member_id) REFERENCES members (id)
);
INSERT INTO member_profiles_bad (member_id, bio) VALUES
  (@profile_member_id, '첫 프로필'),
  (@profile_member_id, '두 번째 프로필');
SELECT member_id, COUNT(*) FROM member_profiles_bad GROUP BY member_id;

member_id | COUNT(*)
1         | 2

같은 회원에게 프로필 두 행이 저장되어 "많아야 하나"라는 요구를 어겼지만, SQL은 아무 오류도 내지 않았습니다. ERD의 1:1 표시는 DDL의 제약이 아니므로, 선언한 관계는 1:1이어도 실제로 강제된 관계는 1:N입니다.

일대일은 외래 키만으로 완성되지 않고, 외래 키의 유일성이 있어야 합니다. 공유 기본 키는 member_id라는 열 하나에 기본 키의 유일성과 외래 키의 참조 제약을 함께 둡니다. 반대로 프로필이 따로 작업의 흐름이나 바깥의 참조를 갖는다면, profile_id의 기본 키에 member_id의 UNIQUE를 더하는 편이 더 유연하므로, 공유 기본 키를 모든 확장 테이블의 관습으로 쓰지는 않습니다.

ERD의 1:1과 DDL의 1:N이 어긋나지 않았는지 볼 때는, 외래 키의 열이 기본 키나 UNIQUE인지, 어느 쪽이 0을 허용하는지가 외래 키의 위치로 나타나는지 확인합니다. 프로필이 회원 없이 옮겨지거나 보존될 수 있는지, 아래의 참조가 member_id와 profile_id 가운데 무엇을 가리켜야 하는지도 정합니다.

외래 키의 위치와 수명

프로필에 member_id의 공유 기본 키이자 외래 키를 두면, 회원은 프로필을 0개나 1개, 프로필은 회원을 정확히 하나 가집니다. 회원의 행에 NULL을 허용하는 profile_id 외래 키를 두는 방향도 가능하지만, 부모가 확장 테이블을 알아야 하고, 선택적인 1:1이 늘 때마다 NULL을 허용하는 열도 늘어납니다.

M:N의 post_tags는 두 외래 키의 조합이 최소의 후보 키입니다. 연결 행을 다른 기능이 참조하지 않는다면 복합 기본 키가 자연스럽고, 검토나 추천의 이력이 그 관계를 따로 참조한다면 post_tags_id에 조합의 UNIQUE를 더한 방식을 고릅니다. 이 글과 다음 글은 이처럼 두 부모의 키를 조합한 복합 기본 키를 도표처럼 조합 기본 키라고 부릅니다.

키를 정할 때는 먼저 존재할 수 있는 주 테이블과 선택적인 확장 테이블을 구별하고, 외래 키에 기본 키나 UNIQUE가 있어 많아야 하나를 강제하는지 봅니다. 출처나 신뢰도 같은 값이 태그 자체가 아니라 게시글과 태그의 조합에 종속되는지, 연결 행에 따로 된 id가 필요한 쓰임이 있는지도 찾습니다.

공유 기본 키와 연결 키

지금의 요구에서는 프로필과 post_tags 모두 부모 없이는 수명이 없고 바깥의 참조도 작으므로, 식별의 키를 고릅니다. 아래 DDL에서 member_profiles는 member_id 하나가 기본 키이자 회원을 가리키는 외래 키이고, post_tags는 따로 된 id 없이 (post_id, tag_id)가 기본 키입니다. 10장에서 만든 post_tags와 tags는 지우고 다시 만들며, 끝의 INSERT는 프로필 하나, 태그 둘, 연결 셋을 넣습니다.

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

CREATE TABLE member_profiles (
  member_id BIGINT UNSIGNED NOT NULL,
  bio VARCHAR(500) NULL,
  preferred_theme VARCHAR(16) NOT NULL DEFAULT 'SYSTEM',
  PRIMARY KEY (member_id),
  FOREIGN KEY (member_id) REFERENCES members (id)
    ON DELETE CASCADE
);
CREATE TABLE tags (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  PRIMARY KEY (id), UNIQUE (name)
);
CREATE TABLE post_tags (
  post_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  source VARCHAR(16) NOT NULL,
  confidence DECIMAL(5,4) NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts (id),
  FOREIGN KEY (tag_id) REFERENCES tags (id),
  CHECK (source IN ('MANUAL', 'AUTO')),
  CHECK (confidence IS NULL OR confidence BETWEEN 0 AND 1)
);

SET @profile_member_id := (SELECT MIN(id) FROM members);
SET @profile_post_1 := (SELECT MIN(id) FROM posts);
SET @profile_post_2 := (
  SELECT MIN(id) FROM posts WHERE id > @profile_post_1
);

INSERT INTO member_profiles (member_id, bio, preferred_theme)
VALUES (@profile_member_id, '회원 게시판 프로필', 'SYSTEM');

INSERT INTO tags (name) VALUES ('mysql');
SET @profile_tag_1 := LAST_INSERT_ID();

INSERT INTO tags (name) VALUES ('modeling');
SET @profile_tag_2 := LAST_INSERT_ID();

INSERT INTO post_tags (post_id, tag_id, source, confidence)
VALUES
  (@profile_post_1, @profile_tag_1, 'MANUAL', NULL),
  (@profile_post_1, @profile_tag_2, 'AUTO', 0.9500),
  (@profile_post_2, @profile_tag_1, 'AUTO', 0.9000);

같은 회원의 두 번째 프로필과 같은 게시글과 태그의 조합은 모두 기본 키가 거부하고, 서로 다른 연결은 자유롭게 저장됩니다. 없는 회원의 프로필은 외래 키가 막으며, 아래의 세 번째 문장은 id가 999999인 회원이 없다고 전제합니다.

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

INSERT INTO post_tags (post_id, tag_id, source)
VALUES (@profile_post_1, @profile_tag_1, 'MANUAL');

INSERT INTO member_profiles (member_id, bio)
VALUES (999999, '고아 프로필');

ERROR 1062 (23000): Duplicate entry '1' for key 'member_profiles.PRIMARY'
ERROR 1062 (23000): Duplicate entry '1-1' for key 'post_tags.PRIMARY'
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`board_lab`.`member_profiles`, CONSTRAINT `member_profiles_ibfk_1` ...)

같은 게시글에 다른 태그를 붙이거나 같은 태그를 다른 게시글에 붙이는 것은 허용됩니다. "출처가 AUTO일 때만 신뢰도가 필수"처럼 열 사이의 규칙은 CHECK로 더 구체화할 수 있고, 자동 추천 모델의 버전이 연결마다 다르다면 post_tags의 속성으로 더합니다.

프로필의 CASCADE는 개인 데이터를 지워 달라는 요구에 맞을 수 있지만, 보존의 규칙을 먼저 확인합니다. 지금 post_tags의 외래 키는 기본 동작이어서, 게시글을 지울 때 CASCADE로 바꿀지는 감사와 추천 이유의 보존 요구와 함께 따로 비교합니다.

카디널리티와 행의 증가를 확인한다

프로필의 중복과 조합의 중복은 제약상 불가능해야 하고, 게시글별 태그 수와 태그별 게시글 수는 양쪽 방향으로 집계합니다. 네 쿼리 가운데 첫째와 넷째는 HAVING COUNT(*) > 1로 둘 이상 겹친 키만 고르는 위반 검사이고, 가운데 둘은 같은 post_tags를 게시글 쪽과 태그 쪽에서 각각 묶어 셉니다. 아래 결과에는 행이 나온 두 집계만 옮겼습니다.

SELECT member_id, COUNT(*) AS profile_count
FROM member_profiles GROUP BY member_id HAVING COUNT(*) > 1;

SELECT post_id, COUNT(*) AS tag_count
FROM post_tags GROUP BY post_id ORDER BY post_id;

SELECT tag_id, COUNT(*) AS post_count
FROM post_tags GROUP BY tag_id ORDER BY tag_id;

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

post_id | tag_count
1       | 2
2       | 1

tag_id | post_count
1      | 2
2      | 1

프로필의 중복과 조합의 중복을 찾는 첫째와 넷째 쿼리는 빈 결과를 돌려주고, 나머지 두 집계는 한 게시글에 여러 태그, 한 태그에 여러 게시글이라는 M:N을 실제의 수로 보여 줍니다. 세 연결은 (첫 게시글, mysql), (첫 게시글, modeling), (둘째 게시글, mysql)입니다.

남은 (둘째 게시글, modeling)을 더하면 두 집계 모두 2와 2가 됩니다. 전체 연결은 3행에서 4행으로 늘지만 프로필이나 태그의 원래 행을 복제하지는 않으며, 같은 태그에 MANUAL과 AUTO가 함께 있는 것도 연결 행마다 따로 기록했기 때문입니다.

공유 기본 키의 프로필은 member_id만 알면 JOIN이 간단하고, 대리 키를 위한 인덱스도 따로 없습니다. 하지만 프로필을 다른 도메인으로 옮기거나 버전마다 여러 행으로 바꾼다면 기본 키의 전략을 다시 검토합니다. 조합 기본 키의 왼쪽 접두어는 게시글에서 태그를 찾는 조회에 맞고, 반대 방향에는 tag_id로 시작하는 인덱스가 필요합니다. InnoDB는 외래 키를 위한 인덱스를 자동으로 만들며 그 레코드에 기본 키의 열을 담으므로, SHOW INDEX와 실행 계획을 보고 겹치는 인덱스를 더하지 않도록 합니다.

1:1의 분리는 자주 바뀌는 프로필의 열이 회원 행의 잠금과 폭에 주는 영향을 떼어 낼 수 있지만, 조회할 때 JOIN의 비용이 생깁니다. 자동 태그를 대량으로 갱신할 때는 수동으로 붙인 행을 덮어쓰지 않도록 출처를 조건에 넣고, 조합에 대한 UPSERT의 규칙을 분명히 합니다.

예외도 확인합니다. 프로필이 없는 회원을 INNER JOIN하면 목록에서 빠지므로, 선택적인 참여를 보여 주는 화면은 LEFT JOIN합니다. 태그 이름의 대소문자가 같은 값인지는 정렬 규칙에 따라 다르고, 신뢰도는 DECIMAL의 소수 자릿수와 1.0000이라는 끝값을 시험합니다. AUTO에서 MANUAL로 바꾸는 것이 같은 조합의 UPDATE인지 별도의 이력 행인지도 요구로 정합니다.

식별과 비식별을 프로젝트 전체의 규칙 하나로 통일하지 않고, 프로필과 댓글, post_tags 각각의 수명과 참조, 변경을 이유로 키를 고릅니다.

정리

• 일대일은 외래 키에 유일성이 있어야 실제로 많아야 하나가 됩니다.
• 완전히 종속된 선택적인 확장에는 공유 기본 키가 간결합니다.
• M:N은 연결 테이블과 외래 키 두 개로 구현하고, 관계의 속성은 연결 행에 둡니다.
• 연결 행을 아래에서 참조하는 일이 많아지면 대리 id로 바꾸는 것을 검토합니다.

연습 문제

1. 알림 설정과 북마크

회원과 notification_settings는 0..1 관계이고, 설정은 회원과 함께 지워집니다. 회원과 게시글은 북마크를 사이에 둔 M:N이며, bookmarked_at과 memo가 연결의 속성입니다. DDL을 설계해 보세요.

DROP TABLE IF EXISTS bookmarks;
DROP TABLE IF EXISTS notification_settings;

CREATE TABLE notification_settings (
  member_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  email_enabled BOOLEAN NOT NULL DEFAULT TRUE,
  FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE CASCADE
);
CREATE TABLE bookmarks (
  member_id BIGINT UNSIGNED NOT NULL,
  post_id BIGINT UNSIGNED NOT NULL,
  bookmarked_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  memo VARCHAR(200) NULL,
  PRIMARY KEY (member_id, post_id),
  FOREIGN KEY (member_id) REFERENCES members (id),
  FOREIGN KEY (post_id) REFERENCES posts (id)
);

2. 프로필을 버전마다 남긴다면

프로필을 수정할 때마다 지난 내용을 남겨야 한다는 요구가 생겼습니다. 공유 기본 키의 member_profiles를 그대로 쓸 수 있나요?

다음 글 회원 게시판 논리 모델에서는 관계마다 다르게 정한 키의 전략을 하나의 논리 모델로 모으고, 그 판단을 설계의 기록으로 남깁니다.