공유 기본 키와 연결 테이블

식별 관계가 항상 나쁜 것은 아닙니다.

member_profiles가 회원 없이 존재할 수 없고 정확히 하나라면 member_id를 프로필 PK이자 FK로 쓰는 공유 기본 키가 1:1을 간결하게 보장합니다.

post_tags 연결도 두 부모 조합 자체가 관계 정체성일 수 있습니다.

프로필은 회원당 0..1, 회원은 프로필 없이 먼저 가입할 수 있습니다.

게시글과 태그는 M:N이며 연결마다 원본과 신뢰도가 있습니다.

관계별 수명과 외부 참조가 달라 키 전략도 다르게 선택합니다.

UNIQUE 없는 일대일 관계

profile_id 대리 PK만 두고 member_id 일반 FK를 사용하면 같은 회원의 프로필 여러 행을 DB가 허용합니다.

ERD의 1:1 표시는 DDL 제약이 아니므로 실제 데이터는 1:N이 됩니다.

일대일 유일성을 강제하지 않는 profile

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에 프로필 두 행이 저장되어 0..1 요구를 위반하지만 SQL 오류는 없습니다.

오류 실행 결과

member_id           | COUNT(*)
<first member id>   | 2

declared relationship: 1:1
enforced relationship: 1:N

일대일은 FK만으로 완성되지 않고 FK의 유일성이 필요합니다.

공유 PK는 member_id가 프로필 PK이므로 유일성과 참조를 한 제약으로 결합합니다.

반대로 프로필이 독립 작업 흐름·외부 참조를 가지면 profile_id PK와 member_id UNIQUE가 더 유연합니다.

공유 PK를 모든 확장 테이블의 관습으로 사용하지 않습니다.

ERD 1:1과 DDL 1:N의 차이 확인

최대 1 — FK 열이 PK 또는 UNIQUE인지 확인합니다.
선택 참여 — 어느 쪽이 0을 허용하는지 FK 위치로 표현합니다.
수명 — 프로필이 회원 없이 이전·보존될 수 있는지 봅니다.
확장 — 하위 참조가 member_id와 profile_id 중 무엇을 의미하는지 정합니다.

외래 키 위치와 수명

프로필에 member_id 공유 PK/FK를 두면 회원은 프로필 0..1, 프로필은 회원 정확히 1입니다.

회원 행에 profile_id NULL 허용 FK를 두면 방향은 가능하지만 부모가 확장 테이블을 알아야 하고 여러 선택 1:1마다 NULL 허용 열이 늘어납니다.

M:N post_tags는 두 FK 조합이 최소 후보 키입니다.

연결 행을 다른 기능이 참조하지 않으면 복합 PK가 자연스럽고, 검토·추천 이력이 관계를 단독 참조하면 post_tags_id+조합 UNIQUE를 선택합니다.

선택 참여와 연결 속성으로 키 정하기

선택 쪽 — 먼저 존재할 수 있는 주 테이블과 선택 확장 테이블을 구분합니다.
유일성 — FK에 PK/UQ가 있어 최대 1을 강제하는지 봅니다.
관계 속성 — 원본·신뢰도가 태그 자체가 아니라 게시글-태그 조합에 종속되는지 봅니다.
하위 참조 — 연결 행이 독립 ID를 필요로 하는 사용 사례를 찾습니다.

공유 기본 키와 연결 키

현재 요구에서는 프로필과 post_tags 모두 부모 없이 수명이 없고 외부 참조도 작으므로 식별 키를 선택합니다.

shared-PK profile과 복합-PK post_tags

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

두 번째 프로필과 같은 게시글-태그 조합은 모두 PK 1062로 거절되고 서로 다른 연결은 자유롭게 저장됩니다.

공유 PK와 관계 조합 중복 실패

INSERT INTO member_profiles (member_id, bio)
VALUES (@profile_member_id, '중복 프로필');
-- ERROR 1062: member_profiles.PRIMARY

INSERT INTO post_tags (post_id, tag_id, source)
VALUES (@profile_post_1, @profile_tag_1, 'MANUAL');
-- ERROR 1062: post_tags.PRIMARY

존재하지 않는 회원 프로필 실패

INSERT INTO member_profiles (member_id, bio)
VALUES (999999, '고아 프로필');
-- ERROR 1452: member_profiles.member_id FK

개선 실행 결과

second profile for first member: ERROR 1062
same post + same tag: ERROR 1062
same post + different tag: allowed
same tag + different post: allowed
profile without member: ERROR 1452

AUTO 원본일 때만 신뢰도 필수라는 컬럼 간 규칙을 검사로 더 구체화할 수 있습니다.

자동 추천 모델 버전이 연결마다 다르면 post_tags 속성으로 추가합니다.

프로필 CASCADE는 개인 데이터 삭제 요구에 맞을 수 있지만 보존 규칙을 확인합니다.

post_tags는 게시글 삭제 시 CASCADE가 합리적인지 감사·추천 근거 데이터 요구와 비교합니다.

프로필·태그 위반 예제 데이터 실행하기

1:1 위반 — 같은 회원 프로필 두 번 삽입을 시도합니다.
M:N 예제 데이터 — 2 게시글×2 태그의 네 가능한 조합을 넣습니다.
관계 속성 — 같은 태그도 게시글별 원본이 다른지 확인합니다.
삭제 — 부모 삭제 정책이 프로필과 태그 연결에 각각 맞는지 테스트합니다.

카디널리티와 행 증가 확인

프로필 중복과 조합 중복은 제약상 불가능해야 하며, 게시글별 태그·태그별 게시글 수를 양방향 집계합니다.

1:1·M:N 결과 검증

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;

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

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

프로필 중복과 조합 중복 쿼리는 0행입니다.

나머지 두 집계는 한 게시글 여러 태그, 한 태그 여러 게시글이라는 M:N을 실제 수로 보여 줍니다.

공유 PK와 역방향 연결 인덱스

공유 PK 프로필은 member_id만 알아도 JOIN이 간단하고 추가 대리 인덱스가 없습니다.

하지만 프로필 자체를 다른 도메인으로 이전하거나 버전별 여러 행으로 바꾸면 PK 전략을 재검토합니다.

연결 테이블의 조합 PK 순서는 게시글→태그 조회에 유리하며 태그→게시글 조회에는 (tag_id, post_id) 보조 인덱스가 필요합니다.

FK가 tag_id 인덱스를 요구하므로 실제 SHOW 인덱스와 중복을 확인합니다.

프로필 행 폭과 자동 태그 갱신

1:1 분리는 자주 바뀌는 프로필 열의 잠금·행 폭을 회원에서 분리할 수 있지만 조회 JOIN 비용이 생깁니다.

자동 태그 대량 갱신은 수동 행을 덮지 않도록 원본을 조건에 포함하고 조합 UPSERT 정책을 명확히 합니다.

1:1·M:N 키 선택표

선택	얻는 효과	감수할 비용	적합한 조건
공유 PK 1:1	유일성·참조 결합	독립 수명 제한	완전 종속 확장일 때
`profile_id`+UQ	독립 참조·변경	인덱스 추가	프로필 작업 흐름이 클 때
조합 PK 연결	관계 정체성 간결	하위 참조 넓음	연결이 단순할 때
연결 ID+UQ	연결 단독 참조	키·인덱스 증가	관계 이력이 확장될 때

두 게시글과 두 태그로 네 조합 만들기

FK 위치 — 회원에 profile_id를 둘 때 선택·확장 비용을 그립니다.
조합 예제 데이터 — 2×2 조합과 중복을 실행합니다.
역방향 인덱스 — 태그→게시글 계획에서 보조 인덱스 전후를 비교합니다.
관계 확장 — 추천 근거·검수 상태가 추가될 때 연결 ID가 필요한지 판단합니다.

NULL 프로필·정렬 규칙·신뢰도 예외

프로필 없는 회원을 INNER JOIN하면 목록에서 누락되므로 선택 참여 화면은 LEFT JOIN합니다.
태그 이름 대소문자 유일성은 정렬 규칙에 따라 달라집니다.
신뢰도 DECIMAL 소수 자릿수와 1.0000 경계를 테스트합니다.
AUTO→MANUAL 전환이 같은 조합 UPDATE인지 별 이력 행인지 요구를 정합니다.

마지막 문서는 관계별로 다른 키 결정을 하나의 논리 모델에 통합하고 현대적 기본값과 예외를 설계 기록으로 남깁니다.

일대일·다대다 키 선택

판단 축	확인할 질문
최대 1	일대일 FK가 PK 또는 UNIQUE로 강제되는가?
선택	선택 확장 쪽에 FK가 있어 NULL 확산을 줄이는가?
종속	확장·연결이 부모 없이 독립 수명을 갖는가?
관계 속성	두 부모 조합에 속한 값을 연결 테이블에 두었는가?
역방향	양방향 조회 인덱스와 하위 참조를 검토했는가?

식별·비식별을 프로젝트 전체 규칙 하나로 통일하지 않습니다.

프로필, 댓글, post_tags 각각의 수명·참조·변경을 근거로 키를 선택합니다.

연습 문제

회원과 notification_settings는 0..1이고 설정은 회원과 함께 삭제됩니다.

회원과 게시글은 북마크를 사이에 둔 M:N이며 bookmarked_at·memo가 연결 속성입니다.

DDL을 설계하세요.

해설과 예시 답안

설정은 member_id 공유 PK, bookmarks는 (member_id, post_id) 조합 PK를 사용합니다.

같은 회원이 같은 게시글을 한 번만 북마크한다면 조합 PK가 관계의 중복을 직접 막습니다.

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

같은 회원의 같은 게시글 북마크 중복이 거절되고 회원별 설정 두 행이 불가능한지 확인합니다.

핵심 정리

일대일은 FK 유일성이 있어야 실제로 최대 1이 됩니다.
완전 종속 선택 확장에는 공유 PK가 간결합니다.
M:N은 연결 테이블과 두 FK로 구현하고 관계 속성을 연결 행에 둡니다.
연결의 하위 참조가 커질 때 대리 ID 전환을 검토합니다.

다음 문서에서는 여러 관계의 키 전략을 하나의 논리 모델로 통합하고 설계 근거를 검증합니다.

이 페이지의 목차