PM의 DB 실습 2편 – 버전 관리와 태그 시스템

불변성 패턴부터 N:M 관계까지, 실행으로 배운 데이터 설계

들어가며

1편에서는 북마크, 댓글, 권한 관리를 통해 1:N 관계와 FK, 복합 UNIQUE, soft delete 등의 DB 실습을 실행했습니다.

이번 2편은 그 연장선입니다. 버전 관리와 태그 시스템, 두 가지 실습을 통해 불변성 패턴과 N:M 관계를 체감해봤습니다.

1편에서 댓글 수정 이력을 comment_history 테이블에 남겼던 것을 기억하신다면, 버전 관리는 그 패턴의 확장입니다. 차이점은 version_number로 명시적인 순서를 매기고, 비교와 복원까지 고려했다는 점입니다.

역시 아직 배우는 단계로 완벽하지는 않지만, 직접 실행해보며 습득한 내용을 있는 그대로 기록했습니다.

실습 3: 버전 관리

시나리오

“정책 문서의 모든 수정 이력을 추적하고, 이전 버전으로 되돌릴 수 있는 시스템을 만든다.”

Google Docs의 “버전 기록” 기능을 떠올리면 됩니다. 어제 삭제한 문단도, 일주일 전 내용도 그대로 보존되는 그 기능. 이것을 DB로 구현하려면 어떤 구조가 필요할지 생각해봤습니다.

시나리오 1: 기본 구조

요구사항:

“재택근무 정책이 수정될 때마다 이전 내용을 보관하고 싶습니다. 나중에 ‘작년엔 뭐라고 되어 있었지?’ 확인하려고요.”

설계 원칙:

• policies 테이블은 항상 최신 버전만 유지
• 수정할 때마다 이전 내용을 policy_versions 테이블에 복사해서 저장

관계 설명:

• users (1) → policies (N): 1명이 여러 정책 작성 가능
• policies (1) → policy_versions (N): 1개 정책이 여러 버전 보유

DBML 코드 (dbdiagram.io 시각화용):

Table users {
  user_id int [pk, increment]
  name varchar [not null]
  email varchar [unique, not null]
  created_at timestamp [default: `now()`]
}

Table policies {
  policy_id int [pk, increment]
  title varchar [not null]
  content text [not null]
  created_by int [ref: > users.user_id, not null]
  created_at timestamp [default: `now()`]
}

Table policy_versions {
  version_id int [pk, increment]
  policy_id int [ref: > policies.policy_id, not null]
  title varchar [not null]
  content text [not null]
  version_number int [not null]
  created_at timestamp [default: `now()`]
}

SQL 코드:

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE policies (
  policy_id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  created_by INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(user_id)
);

CREATE TABLE policy_versions (
  version_id INTEGER PRIMARY KEY AUTOINCREMENT,
  policy_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  version_number INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (policy_id) REFERENCES policies(policy_id)
);

-- 샘플 데이터: 사용자
INSERT INTO users (name, email) VALUES
  ('김철수', 'kim@company.com'),
  ('이영희', 'lee@company.com'),
  ('박민수', 'park@company.com');

-- 샘플 데이터: 정책 (최신 버전만)
INSERT INTO policies (title, content, created_by, created_at) VALUES
  ('재택근무 지침', '대면+재택근무 혼합 가능. 사전 신청 필수.', 3, '2024-07-30 14:30:00');

-- 샘플 데이터: 버전 이력
INSERT INTO policy_versions (policy_id, title, content, version_number, created_at) VALUES
  (1, '재택근무 지침', '주 2회 재택근무 가능. 사전 신청 필수.', 1, '2024-01-01 09:00:00'),
  (1, '재택근무 지침', '주 3회 이상 재택근무 가능. 사전 신청 필수.', 2, '2024-06-15 14:30:00'),
  (1, '재택근무 지침', '대면+재택근무 혼합 가능. 사전 신청 필수.', 3, '2024-07-30 14:30:00');

조회 쿼리:

-- 1. 정책 1번의 전체 버전 이력
SELECT
  version_number AS '버전',
  title AS '제목',
  content AS '내용',
  created_at AS '수정일시'
FROM policy_versions
WHERE policy_id = 1
ORDER BY version_number;

-- 2. 최초 vs 최신 버전 비교
SELECT
  version_number AS '버전',
  content AS '내용'
FROM policy_versions
WHERE policy_id = 1
  AND version_number IN (1, 3);

-- 3. 원본 테이블 vs 이력 테이블 개수
SELECT
  (SELECT COUNT(*) FROM policies) AS '현재_정책수',
  (SELECT COUNT(*) FROM policy_versions) AS '전체_버전수';

조회 결과에서 원본 정책은 1개인데 버전은 3개입니다. 이 구조가 핵심입니다. policies 테이블은 화면에 보여줄 최신 내용만 담고, 과거가 궁금할 때는 policy_versions를 조회합니다.

이때 중요한 개념이 하나 있습니다. 수정 = 삭제가 아니라 추가입니다.

기존 내용을 지우는 게 아니라 새로운 행을 추가하는 방식으로 이력을 쌓아갑니다. 1편에서 배운 1:N 관계가 버전 관리에서도 동일하게 적용됩니다.

시나리오 2: 수정자 추적

추가 요구사항:

“누가 수정했는지도 기록해주세요. 나중에 ‘이거 왜 바뀐 거야?’ 물어볼 사람을 알아야 해요.”

policy_versions 테이블에 created_by 컬럼을 추가하고 users 테이블과 FK로 연결합니다.

DBML 코드:

Table users {
  user_id int [pk, increment]
  name varchar [not null]
  email varchar [unique, not null]
  created_at timestamp [default: `now()`]
}

Table policies {
  policy_id int [pk, increment]
  title varchar [not null]
  content text [not null]
  created_by int [ref: > users.user_id, not null]
  created_at timestamp [default: `now()`]
}

Table policy_versions {
  version_id int [pk, increment]
  policy_id int [ref: > policies.policy_id, not null]
  title varchar [not null]
  content text [not null]
  version_number int [not null]
  created_by int [ref: > users.user_id, not null]
  created_at timestamp [default: `now()`]
}

변경 사항:

created_by 컬럼이 추가되었고, users 테이블과 FK 관계가 새로 생겼습니다. 이제 users → policy_versions 방향의 1:N 관계가 추가됩니다.

SQL 코드 (테이블 재생성):

DROP TABLE IF EXISTS policy_versions;
DROP TABLE IF EXISTS policies;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE policies (
  policy_id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  created_by INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(user_id)
);

CREATE TABLE policy_versions (
  version_id INTEGER PRIMARY KEY AUTOINCREMENT,
  policy_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  version_number INTEGER NOT NULL,
  created_by INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (policy_id) REFERENCES policies(policy_id),
  FOREIGN KEY (created_by) REFERENCES users(user_id)
);

-- 샘플 데이터: 사용자
INSERT INTO users (name, email) VALUES
  ('김철수', 'kim@company.com'),
  ('이영희', 'lee@company.com'),
  ('박민수', 'park@company.com');

-- 샘플 데이터: 정책 (최신 버전)
INSERT INTO policies (title, content, created_by, created_at) VALUES
  ('재택근무 지침', '주 3회 이상 재택근무 가능. 팀장 승인 필요.', 2, '2024-09-01 10:00:00');

-- 샘플 데이터: 버전 이력 (수정자 포함)
INSERT INTO policy_versions (policy_id, title, content, version_number, created_by, created_at) VALUES
  (1, '재택근무 지침', '주 2회 재택근무 가능. 사전 신청 필수.', 1, 1, '2024-01-01 09:00:00'),
  (1, '재택근무 지침', '주 3회 이상 재택근무 가능. 사전 신청 필수.', 2, 1, '2024-06-15 14:30:00'),
  (1, '재택근무 지침', '주 3회 이상 재택근무 가능. 팀장 승인 필요.', 3, 2, '2024-09-01 10:00:00');

조회 쿼리:

-- 1. 정책 1번의 수정 이력 + 수정자 이름
SELECT
  pv.version_number AS '버전',
  pv.content AS '내용',
  u.name AS '수정자',
  pv.created_at AS '수정일시'
FROM policy_versions pv
JOIN users u ON pv.created_by = u.user_id
WHERE pv.policy_id = 1
ORDER BY pv.version_number;

-- 2. 이영희가 수정한 모든 버전 찾기
SELECT
  p.title AS '정책명',
  pv.version_number AS '버전',
  pv.created_at AS '수정일시'
FROM policy_versions pv
JOIN policies p ON pv.policy_id = p.policy_id
JOIN users u ON pv.created_by = u.user_id
WHERE u.name = '이영희';

-- 3. 각 사용자별 수정 횟수
SELECT
  u.name AS '수정자',
  COUNT(*) AS '수정_횟수'
FROM policy_versions pv
JOIN users u ON pv.created_by = u.user_id
GROUP BY u.name
ORDER BY COUNT(*) DESC;

policy_versions 테이블만 보면 created_by=1이라는 숫자만 보입니다. users 테이블과 JOIN하면 “김철수”라는 의미 있는 정보가 됩니다. 1편에서 배운 FK와 JOIN의 실전 활용이 여기서도 그대로 등장합니다.

이 구조는 감사 로그(Audit Log) 역할을 합니다. “누가, 언제, 무엇을” 세 가지를 모두 추적할 수 있어서, “작년 9월에 재택근무 규정 누가 바꿨죠?”라는 질문에 쿼리 한 줄로 답할 수 있습니다.

시나리오 3: 버전 비교 및 복원

추가 요구사항:

“v2와 v3를 나란히 비교하고, v3가 문제 있으면 v2로 되돌리고 싶습니다.”

여기서 중요한 설계 결정이 하나 있습니다. 같은 정책의 같은 버전 번호가 중복 저장되는 문제를 막아야 한다는 것입니다.

잘못된 예시:

policy_id=1, version_number=2 (2024-06-15)
policy_id=1, version_number=2 (2024-09-01) ← 중복

복합 UNIQUE (policy_id, version_number)로 이 문제를 DB 레벨에서 막습니다. 1편에서 배운 복합 UNIQUE가 여기서도 동일하게 적용됩니다.

DBML 코드 (최종 버전):

Table users {
  user_id int [pk, increment]
  name varchar [not null]
  email varchar [unique, not null]
  created_at timestamp [default: `now()`]
}

Table policies {
  policy_id int [pk, increment]
  title varchar [not null]
  content text [not null]
  created_by int [ref: > users.user_id, not null]
  created_at timestamp [default: `now()`]
}

Table policy_versions {
  version_id int [pk, increment]
  policy_id int [ref: > policies.policy_id, not null]
  title varchar [not null]
  content text [not null]
  version_number int [not null]
  created_by int [ref: > users.user_id, not null]
  created_at timestamp [default: `now()`]

  indexes {
    (policy_id, version_number) [unique]
  }
}

1단계: 테이블 생성 + 샘플 데이터

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE policies (
  policy_id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  created_by INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(user_id)
);

CREATE TABLE policy_versions (
  version_id INTEGER PRIMARY KEY AUTOINCREMENT,
  policy_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  version_number INTEGER NOT NULL,
  created_by INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (policy_id) REFERENCES policies(policy_id),
  FOREIGN KEY (created_by) REFERENCES users(user_id),
  UNIQUE(policy_id, version_number)
);

INSERT INTO users (name, email) VALUES
  ('김철수', 'kim@company.com'),
  ('이영희', 'lee@company.com'),
  ('박민수', 'park@company.com');

INSERT INTO policies (title, content, created_by, created_at) VALUES
  ('재택근무 지침', '주 3회 재택근무 가능. 팀장 승인 필요. 신청은 전날 18시까지.', 2, '2024-11-20 15:00:00'),
  ('보안 정책', '업무용 PC는 회사망에서만 접속 가능. VPN 필수.', 3, '2024-10-01 09:00:00');

INSERT INTO policy_versions (policy_id, title, content, version_number, created_by, created_at) VALUES
  (1, '재택근무 지침', '주 2회 재택근무 가능. 사전 신청 필수.', 1, 1, '2024-01-01 09:00:00'),
  (1, '재택근무 지침', '주 3회 이상 재택근무 가능. 사전 신청 필수.', 2, 1, '2024-06-15 14:30:00'),
  (1, '재택근무 지침', '주 3회 이상 재택근무 가능. 팀장 승인 필요.', 3, 2, '2024-09-01 10:00:00'),
  (1, '재택근무 지침', '주 3회 재택근무 가능. 팀장 승인 필요. 신청은 전날 18시까지.', 4, 2, '2024-11-20 15:00:00'),
  (2, '보안 정책', '업무용 PC는 회사망에서만 접속 가능.', 1, 3, '2024-10-01 09:00:00'),
  (2, '보안 정책', '업무용 PC는 회사망에서만 접속 가능. VPN 필수.', 2, 3, '2024-10-15 11:00:00');

-- 확인
SELECT * FROM policy_versions WHERE policy_id = 1 ORDER BY version_number;

복합 UNIQUE 제약 테스트:

-- 테스트 1: 정상 입력 (성공)
INSERT INTO policy_versions (policy_id, title, content, version_number, created_by)
VALUES (1, '테스트1', '내용', 5, 1);

-- 테스트 2: 중복 시도 (실패 예상)
INSERT INTO policy_versions (policy_id, title, content, version_number, created_by)
VALUES (1, '중복시도', '내용', 5, 1);

-- 테스트 3: 다른 정책의 같은 버전 번호 (성공)
INSERT INTO policy_versions (policy_id, title, content, version_number, created_by)
VALUES (2, '보안정책v5', '내용', 5, 1);

-- 결과 확인
SELECT
  policy_id AS '정책ID',
  version_number AS '버전',
  title AS '제목'
FROM policy_versions
WHERE version_number = 5
ORDER BY policy_id;

테스트 2에서 UNIQUE constraint failed: policy_versions.policy_id, policy_versions.version_number 에러가 나야 정상입니다.

테스트 3은 정책 ID가 다르기 때문에 같은 버전 번호 5여도 통과됩니다. policy_id와 version_number의 조합이 유일해야 한다는 의미입니다.

2단계: 복원 실행 (v2 내용을 v5로 생성)

-- v2 내용으로 policies 테이블 최신 내용 업데이트
UPDATE policies
SET content = '주 3회 이상 재택근무 가능. 사전 신청 필수.'
WHERE policy_id = 1;

-- v2 내용을 v5로 새 버전 생성
INSERT INTO policy_versions (policy_id, title, content, version_number, created_by, created_at)
VALUES (
  1,
  '재택근무 지침',
  '주 3회 이상 재택근무 가능. 사전 신청 필수.',
  5,
  2,
  CURRENT_TIMESTAMP
);

-- 결과 확인
SELECT
  version_number AS '버전',
  content AS '내용',
  created_by AS '수정자ID',
  created_at AS '수정일시'
FROM policy_versions
WHERE policy_id = 1
ORDER BY version_number;

3단계: policies 테이블 확인

SELECT
  policy_id AS '정책ID',
  title AS '제목',
  content AS '현재내용',
  created_by AS '수정자ID'
FROM policies
WHERE policy_id = 1;

여기서 핵심 개념은 복원 ≠ 되돌리기입니다.

되돌리기는 v4를 삭제해서 v3 상태로 만드는 방식입니다. 복원은 v4를 그대로 두고 v2 내용으로 v5를 새로 만드는 방식입니다. 이력을 절대 삭제하거나 수정하지 않고 새로운 행만 추가합니다. 이것이 불변성(Immutability) 패턴입니다.

버전 관리 실습에서 배운 것

복합 UNIQUE의 의미가 명확해졌습니다.

단일 UNIQUE는 email처럼 컬럼 하나가 유일하면 되는 경우입니다. (policy_id, version_number) 복합 UNIQUE는 두 값의 조합이 유일해야 한다는 의미입니다. policy_id만으로는 여러 버전이 존재하고, version_number만으로는 여러 정책이 같은 번호를 쓸 수 있습니다. 둘의 조합이 핵심입니다.

실습하면서 PM 관점에서 미리 정해야 할 것들도 보였습니다.

버전은 저장할 때마다 생성할지, 큰 변경만 저장할지, 수동으로 저장할지는 기획 단계에서 결정해야 합니다. 보관 기간, 복원 정책, 동시 수정 충돌 처리 방법도 마찬가지입니다.

개발자에게 “어떻게”를 물어보기 전에 PM이 먼저 “무엇을, 왜”를 정의해야 한다는 걸 느꼈습니다.

실습 4: 태그 시스템 (N:M 관계)

시나리오

“정책에 여러 태그를 달고, 태그로 검색한다.”

시나리오 1: 문자열로 저장하면 생기는 문제

직접 실습할 필요는 없지만 왜 이 방식이 안 되는지는 짚고 넘어가야 합니다.

-- ❌ 이렇게 하면 안 됨
-- policy_tags VARCHAR -- "재택근무,인사,2024" 이렇게 한 컬럼에 저장

-- 문제 1: 검색
-- WHERE policy_tags LIKE '%재택근무%' → 데이터 많아지면 전체 테이블 스캔
-- 문제 2: 수정
-- 태그 하나만 바꾸려면 문자열 파싱해서 직접 처리해야 함
-- 문제 3: 통계
-- "#재택근무 태그가 몇 개 정책에 붙었나?" 집계 사실상 불가능

1편에서 반복했던 문자열 저장 문제가 태그에서도 동일하게 나타납니다. 이를 해결하려면 테이블을 분리해야 합니다.

시나리오 2: N:M 구조 구현과 양방향 조회

1:N은 직관적입니다. 댓글은 하나의 게시글에만 속하기 때문입니다. 하지만 태그는 다릅니다.

• #재택근무 태그 → 정책 A, B, C에 모두 달 수 있음
• 정책 A → #재택근무, #인사, #2024 여러 태그가 모두 붙을 수 있음

양쪽이 서로 여러 개를 가질 수 있는 구조, 이것이 N:M입니다. DB에서는 N:M을 직접 표현할 수 없어서 중간 테이블(Junction Table)을 만들어 우회하는 방식으로 사용합니다.

DBML 코드 (dbdiagram.io 시각화용):

Table policies {
  policy_id int [pk, increment]
  title varchar(200) [not null]
  content text
  created_by int
  created_at timestamp
}

Table tags {
  tag_id int [pk, increment]
  tag_name varchar(50) [unique, not null]
}

Table policy_tags {
  policy_tag_id int [pk, increment]
  policy_id int [not null]
  tag_id int [not null]
  tagged_at timestamp
  tagged_by int // 태그를 단 사용자 ID (users 테이블 참조)

  indexes {
    (policy_id, tag_id) [unique]
  }
}

Ref: policy_tags.policy_id > policies.policy_id
Ref: policy_tags.tag_id > tags.tag_id

ERD에서 확인할 포인트는 두 가지입니다. policy_tags가 policies와 tags 양쪽에 각각 > 방향으로 연결되어 있습니다. 1:N이 두 개 합쳐진 구조가 시각적으로 보입니다. N:M은 중간 테이블을 반드시 거쳐야 한다는 것이 ERD에서 드러납니다.

policy_tags 한 row에는 policy_id 하나, tag_id 하나만 들어갑니다. 태그 여러 개를 한 row에 넣는 것이 아니라, 태그 개수만큼 row가 늘어나는 구조입니다.

policy_tag_id=1  policy_id=1  tag_id=1  (정책1 → #재택근무)
policy_tag_id=2  policy_id=1  tag_id=2  (정책1 → #인사)
policy_tag_id=3  policy_id=1  tag_id=3  (정책1 → #2024)

N:M은 policy_tags 테이블 내부 구조가 아니라, policies와 tags 두 테이블 사이의 관계를 전체적으로 봤을 때 나오는 개념입니다.

policies 기준으로는 정책 1번이 tag_id 1, 2, 3을 가집니다. tags 기준으로는 tag_id=1(#재택근무)이 정책 1번에도, 2번에도 붙습니다. 이 두 방향의 1:N이 동시에 성립하기 때문에 N:M 구조입니다.

테이블 생성:

-- policies 테이블이 없다면 먼저 생성
CREATE TABLE policies (
  policy_id INTEGER PRIMARY KEY AUTOINCREMENT,
  title VARCHAR(200) NOT NULL,
  content TEXT,
  created_by INTEGER,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 태그 테이블
CREATE TABLE tags (
  tag_id INTEGER PRIMARY KEY AUTOINCREMENT,
  tag_name VARCHAR(50) UNIQUE NOT NULL
);

-- 중간 테이블 (복합 UNIQUE 없이 먼저 생성)
CREATE TABLE policy_tags (
  policy_tag_id INTEGER PRIMARY KEY AUTOINCREMENT,
  policy_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  FOREIGN KEY (policy_id) REFERENCES policies(policy_id),
  FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
);

tags에서 UNIQUE가 핵심입니다. #재택근무는 DB 안에 딱 하나만 존재해야 합니다. 여러 정책이 이 하나를 참조하는 구조입니다.

policy_tags는 관계만 저장합니다. 데이터가 아니라 관계를 저장하는 테이블입니다.

샘플 데이터 삽입:

FK 때문에 순서가 중요합니다. policies → tags 순으로 먼저 데이터를 넣고 나서 policy_tags에 연결 데이터를 넣어야 합니다. 순서가 바뀌면 FK 에러가 납니다.

-- policies 데이터 (없다면 먼저 삽입)
INSERT INTO policies (title, content, created_by) VALUES
  ('재택근무 운영 정책', '재택근무 관련 규정 내용', 1),
  ('복리후생 안내', '복리후생 제도 안내 내용', 1);

-- 태그 생성
INSERT INTO tags (tag_name) VALUES
  ('#재택근무'),
  ('#인사'),
  ('#2024'),
  ('#복리후생');

-- 정책에 태그 연결
INSERT INTO policy_tags (policy_id, tag_id) VALUES
  (1, 1),  -- 정책1 → #재택근무
  (1, 2),  -- 정책1 → #인사
  (1, 3),  -- 정책1 → #2024
  (2, 1),  -- 정책2 → #재택근무
  (2, 4);  -- 정책2 → #복리후생

양방향 조회 실습:

-- 방향 1: "정책 1번에 붙은 태그 전부 보여줘"
SELECT p.title, t.tag_name
FROM policies p
JOIN policy_tags pt ON p.policy_id = pt.policy_id
JOIN tags t ON pt.tag_id = t.tag_id
WHERE p.policy_id = 1;

-- 방향 2: "#재택근무 태그가 붙은 정책 전부 보여줘"
SELECT t.tag_name, p.title
FROM tags t
JOIN policy_tags pt ON t.tag_id = pt.tag_id
JOIN policies p ON pt.policy_id = p.policy_id
WHERE t.tag_name = '#재택근무';

-- 통계: "태그별로 몇 개 정책에 쓰였나?"
SELECT t.tag_name, COUNT(pt.policy_id) AS policy_count
FROM tags t
JOIN policy_tags pt ON t.tag_id = pt.tag_id
GROUP BY t.tag_id, t.tag_name
ORDER BY policy_count DESC;

JOIN이 두 번 일어납니다. policies → policy_tags → tags 순으로 다리를 두 번 건넙니다. N:M은 중간 테이블을 반드시 거쳐야 해서 JOIN이 항상 2번 필요합니다.

방향을 반대로 바꾸면 tags → policy_tags → policies 순으로 건너가며 양방향 조회가 모두 됩니다. 이것이 N:M 구조의 강점입니다.

문자열로 저장했으면 통계 쿼리 자체가 불가능했을 것입니다. 중간 테이블로 분리했기 때문에 COUNT 한 줄로 해결됩니다.

시나리오 3: 복합 UNIQUE와 PM 관점 심화

문제 먼저 확인:

-- 같은 조합을 두 번 넣기
INSERT INTO policy_tags (policy_id, tag_id) VALUES (1, 1);
INSERT INTO policy_tags (policy_id, tag_id) VALUES (1, 1);

-- 확인
SELECT * FROM policy_tags WHERE policy_id = 1 AND tag_id = 1;
-- 같은 행이 여러 개 생성됨

정책 1번에 #재택근무가 두 번 태그되는 것이 가능한 상태입니다(기존 입력값까지 포함하여 3개). DB 레벨에서 막아야 합니다.

테이블 재생성 (복합 UNIQUE + 메타데이터 최종 버전):

-- 기존 테이블 먼저 삭제
DROP TABLE policy_tags;

-- 복합 UNIQUE + 메타데이터 포함한 최종 버전으로 재생성
CREATE TABLE policy_tags (
  policy_tag_id INTEGER PRIMARY KEY AUTOINCREMENT,
  policy_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  tagged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 언제 달았나
  tagged_by INTEGER,                               -- 누가 달았나
  FOREIGN KEY (policy_id) REFERENCES policies(policy_id),
  FOREIGN KEY (tag_id) REFERENCES tags(tag_id),
  UNIQUE (policy_id, tag_id)
);

-- 데이터 다시 삽입
INSERT INTO policy_tags (policy_id, tag_id) VALUES
  (1, 1),
  (1, 2),
  (1, 3),
  (2, 1),
  (2, 4);

복합 UNIQUE 작동 확인:

-- 이미 있는 조합을 다시 넣기
INSERT INTO policy_tags (policy_id, tag_id) VALUES (1, 1);
-- UNIQUE constraint failed: policy_tags.policy_id, policy_tags.tag_id 에러가 나야 정상

policy_id=1, tag_id=1 조합이 이미 있으면 에러를 던집니다. 1편의 5개 컬럼 복합 UNIQUE와 원리가 완전히 같습니다. 컬럼 수만 다를 뿐입니다.

tagged_at에는 현재 시각이 자동으로 찍히고, tagged_by는 값을 넣지 않았으니 NULL로 표시됩니다. 1편에서 배운 NULL의 두 가지 의미 중 “값이 없음”에 해당합니다.

중간 테이블에 tagged_at, tagged_by 같은 메타데이터를 붙이면 관계 자체에 대한 이력도 추적할 수 있습니다. “이 태그를 언제, 누가 달았나”까지 기록하는 겁니다. 버전 관리 실습에서 배운 이력 추적 패턴이 N:M 중간 테이블에도 그대로 적용됩니다.

Q. ‘메타데이터’란?

메타데이터는 관계 자체에 대한 부가 정보입니다.

policy_tags는 원래 “정책 1번이 태그 1번과 연결됐다”는 관계만 저장하면 됩니다. 그런데 거기에 tagged_at(언제), tagged_by(누가)를 추가하면 관계가 생긴 시점과 주체까지 기록할 수 있습니다.

이 추가 정보들이 여기서 말하는 메타데이터입니다. 관계의 내용이 아니라 관계에 대한 정보라는 의미입니다.

PM 관점에서 반드시 짚어야 할 것들

태그 삭제 시 연결 처리를 기획 단계에서 결정해야 한다

#재택근무 태그를 삭제하면 이 태그가 달린 정책 30개는 어떻게 될까요? policy_tags row를 자동 삭제할지, 아니면 사용 중인 태그는 삭제 자체를 막을지 정해야 합니다.

“태그 삭제 시 연결된 정책에서도 자동 제거되나요, 아니면 사용 중인 태그는 삭제 불가로 막나요?” 이 질문을 개발자에게 먼저 던질 수 있어야 합니다.

태그 입력 UX 방식이 DB 구조를 결정한다

자유형 입력이냐 선택형이냐에 따라 tags 테이블 관리 방식이 달라집니다.

자유형이면 오타로 #재택근무랑 #재택 근무가 별개 태그로 쌓이는 문제가 생겨서 입력 시 정규화 처리가 필요하고, 선택형이면 tags 테이블에 태그를 추가하거나 삭제할 수 있는 관리자 권한을 기획해야 합니다.

1편에서 배운 권한 관리 개념이 여기서 다시 연결됩니다.

배운 점

같은 패턴의 반복

버전 관리와 태그 시스템을 실습하면서 1편에서 배운 개념들이 반복적으로 등장했습니다.

FK와 JOIN, 복합 UNIQUE, 이력 추적 패턴, NULL의 의미, 문자열 저장의 한계 등입니다. 새로운 개념을 배웠다기보다는 같은 개념이 다른 맥락에서 어떻게 적용되는지를 확인하는 과정에 가까웠습니다.

불변성 패턴

버전 관리 실습에서 가장 인상적이었던 개념은 불변성이었습니다. 기존 데이터를 절대 수정하거나 삭제하지 않고 새로운 행을 추가하는 방식으로 이력을 쌓아갑니다.

N:M은 관계의 개념

N:M은 테이블 내부 구조가 아니라 두 테이블 사이의 관계를 전체적으로 봤을 때 나오는 개념이었습니다. 중간 테이블 자체는 단순히 두 개의 1:N을 연결하는 역할을 합니다.

설계가 비즈니스 로직을 결정한다

1편에서도 느꼈지만, 버전 관리 실습에서 다시 한번 확인했습니다.

복원을 “v4 삭제”로 설계하면 이력이 사라지고, “v5 생성”으로 설계하면 이력이 보존됩니다. 어떻게 설계하느냐에 따라 비즈니스 로직이 완전히 달라집니다.

PM 관점에서의 시사점

요구사항이 데이터 구조로 전환되는 과정을 이해한다

“버전 기록을 보여주세요”라는 요구사항이 policy_versions 테이블로 전환됩니다. “태그로 검색할 수 있어야 해요”가 tags + policy_tags 중간 테이블로 전환됩니다.

이 전환 과정을 이해하면 기획 단계에서 놓치는 부분이 조금씩 줄어들 것 같습니다.

복원 정책처럼 PM이 먼저 정의해야 할 것들이 있다

개발자는 “복원하면 버전 번호를 어떻게 매길까요?”라고 묻습니다. DB 구조를 모르면 “그냥 이전 버전으로 돌아가면 되지 않나요?”라고 막연하게 답하게 됩니다.

불변성 패턴을 이해하면 “v4는 유지하고 v2 내용으로 v5를 새로 생성하면 됩니다”라고 구체적으로 답할 수 있습니다.

태그 기능 하나에도 여러 결정이 필요하다

태그 입력 방식, 태그 삭제 시 연결 처리, 태그 검색 성능. 화면을 그리기 전에 이런 결정들이 먼저 이루어져야 합니다.

DB 구조를 조금이라도 이해하면 이런 질문을 기획 단계에서 미리 꺼낼 수 있습니다.

마치며

3주간의 DB 실습을 통해 1:N, FK, 복합 UNIQUE, soft delete, 이력 추적, 불변성 패턴, N:M 관계까지 직접 실행해봤습니다. 솔직히 단기간에 완벽하게 이해하기에는 어려운 내용입니다. 여전히 모르는 것이 많고, 실무에서 부딪혀야 비로소 체감할 것들도 있을 겁니다.

그래도 ERD를 보고 구조를 어느 정도 읽을 수 있게 됐고, 개발자가 “복합 UNIQUE”나 “Junction Table”이라고 말했을 때 같은 그림을 떠올릴 수 있게 된 것 같습니다. 기획 단계에서 DB 구조 관점을 한 번쯤 고려해볼 수 있게 됐다는 것만으로도 이번 실습은 의미가 있었습니다.

“화면에는 이렇게 보이면 돼요”가 아니라 “이 데이터는 이런 구조로 저장되고, 이런 조건으로 조회되면 될 것 같아요”라고 한마디 덧붙일 수 있게 되는 것만으로도 충분하지 않을까 싶습니다.

참고: 전체 SQL 코드

실습 방법:

1. DROP TABLE IF EXISTS 구문으로 기존 테이블 먼저 삭제
2. CREATE TABLE 구문을 순서대로 실행 (참조되는 테이블 먼저)
3. INSERT 구문으로 샘플 데이터 입력
4. SELECT 쿼리로 결과 확인

주의사항:

• 테이블 생성/삭제 순서 중요: 참조하는 테이블(FK가 있는 쪽)을 나중에 생성하고 먼저 삭제
• FK 제약조건이 있어 참조 테이블이 없으면 에러 발생
• 샘플 데이터는 개념 이해용이므로 자유롭게 수정 가능
• sqlite-online.com 이용 시 단계별로 에디터를 비우고 해당 코드만 붙여넣어 실행

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE policies (
  policy_id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  created_by INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(user_id)
);

CREATE TABLE policy_versions (
  version_id INTEGER PRIMARY KEY AUTOINCREMENT,
  policy_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  version_number INTEGER NOT NULL,
  created_by INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (policy_id) REFERENCES policies(policy_id),
  FOREIGN KEY (created_by) REFERENCES users(user_id),
  UNIQUE(policy_id, version_number)
);

CREATE TABLE policies (
  policy_id INTEGER PRIMARY KEY AUTOINCREMENT,
  title VARCHAR(200) NOT NULL,
  content TEXT,
  created_by INTEGER,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
  tag_id INTEGER PRIMARY KEY AUTOINCREMENT,
  tag_name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE policy_tags (
  policy_tag_id INTEGER PRIMARY KEY AUTOINCREMENT,
  policy_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  tagged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  tagged_by INTEGER,
  FOREIGN KEY (policy_id) REFERENCES policies(policy_id),
  FOREIGN KEY (tag_id) REFERENCES tags(tag_id),
  UNIQUE (policy_id, tag_id)
);

다음 단계:

• 샘플 데이터를 INSERT하여 실제 동작 확인
• 글에서 설명한 SELECT 쿼리를 실행하여 결과 비교
• 자신만의 시나리오로 데이터를 변경하며 실험

참고:

• dbdiagram.io에서 DBML 코드로 ERD 시각화 가능
• 실습 중 에러 발생 시 FK 제약조건과 테이블 생성 순서 확인

— Lane