PM의 DB 실습 1편 – 북마크와 권한 관리

1:N 관계부터 복합 UNIQUE까지, 실행으로 배운 데이터 설계

들어가며

PM이 SQL을 배워야 할까? 개발자가 아닌데 DB 구조까지 알아야 할까?

이 질문에 대한 답을 찾기 위해 3주간 직접 DB 실습을 계획하여 진행했습니다.

실무에서 데이터 구조를 이해하지 못해 개발자와 소통이 막히고 기획이 자주 뒤집히는 경험을 했기에, 이번 기회에 맥락을 이해하고 업무에 활용할 수 있도록 배워보기로 했습니다.

직접 해보니 PM이 “비즈니스 요구사항이 데이터 구조로 어떻게 전환되는지” 조금이라도 이해하면, 개발자와의 소통이 조금 더 명확해질 수 있겠다는 것을 느꼈습니다.

이번 글에는 “정책 관리 플랫폼”이라는 가상의 서비스를 설계하며 배운 내용을 정리했습니다. 북마크 기능과 권한 관리, 두 가지 실습을 통해 1:N 관계, FK(외래키), 복합 UNIQUE, soft delete, 이력 추적 패턴 등을 체감했습니다.

당연히 아직 배우는 단계라 완벽하지는 않습니다. 하지만 “화면만 그리는 기획자”가 아니라 “데이터 구조를 고민하는 PM”이 되기 위한 과정 중 하나로 기록해둡니다.

---

실습 1: 북마크 기능

시나리오

“사용자가 관심 있는 정책을 북마크할 수 있다.”

단순해 보이는 요구사항이지만, 실제로 테이블을 설계하려니 여러 질문이 생겼습니다.

• 북마크를 users 테이블에 TEXT 컬럼으로 넣으면 안 될까?
• 여러 사용자가 같은 정책을 북마크하면 어떻게 저장하지?
• 같은 사용자가 실수로 같은 정책을 2번 북마크하면?

잘못된 설계 (처음 시도)

처음에는 users 테이블에 bookmarks TEXT 컬럼을 추가해서 아래와 같이 저장하려고 했습니다:

잘못된 설계 예시:

• user_id=1 (김철수): bookmarks=”1,3,5″ (정책 1번, 3번, 5번)
• user_id=2 (이영희): bookmarks=”2,4″ (정책 2번, 4번)

문제점:

• 특정 정책을 북마크한 사용자 검색 불가 (LIKE 써야 함 = 느림)
• 정책이 삭제되어도 “1,3,5” 문자열에 그대로 남음
• 언제 북마크했는지 기록할 수 없음
• 북마크 개수 집계 어려움 (문자열 파싱 필요)

그래서 별도의 테이블을 추가 생성하여 users 테이블의 user_id를 참조하는 방식으로 구조를 수정했습니다.

올바른 설계 (별도 테이블 분리)

bookmarks 테이블을 별도로 만들면:

CREATE TABLE bookmarks (
  bookmark_id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  policy_id INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (policy_id) REFERENCES policies(policy_id),
  UNIQUE(user_id, policy_id)
);

핵심 개념 설명

1. FOREIGN KEY (FK, 외래키)

FOREIGN KEY (user_id) REFERENCES users(user_id)

• bookmarks 테이블의 user_id는 users 테이블의 user_id를 “참조”한다는 의미
• users 테이블에 없는 user_id는 bookmarks에 저장할 수 없음
• 덕분에 존재하지 않는 사용자 id로 북마크가 생성되는 상황을 DB 단에서 막을 수 있음

2. 복합 UNIQUE 제약

UNIQUE(user_id, policy_id)

무엇을 방지하는가?

• 같은 사용자(user_id=1)가 같은 정책(policy_id=3)을 2번 북마크하는 실수

어떻게 작동하는가?

✅ 허용되는 경우:

• (user_id=1, policy_id=1): 김철수 – 정책1
• (user_id=1, policy_id=3): 김철수 – 정책3 (user_id=1 반복 가능)
• (user_id=2, policy_id=1): 이영희 – 정책1 (policy_id=1 반복 가능)

❌ 차단되는 경우:

• (user_id=1, policy_id=1): 김철수 – 정책1을 또 추가하려고 하면?
  • DB 에러 발생: “UNIQUE constraint failed”
  • (1, 1) 조합이 이미 존재하기 때문

정리:

• user_id=1은 여러 번 나올 수 있음 (여러 정책 북마크 가능)
• policy_id=1도 여러 번 나올 수 있음 (여러 사람이 북마크 가능)
• 하지만 (user_id=1, policy_id=1) 조합은 단 한 번만 허용

3. created_at 컬럼의 역할

• 언제 북마크했는지 시점 기록
• “최근 북마크한 정책 순”으로 정렬 가능
• 통계: “이번 주 북마크 수” 집계 가능

개선 효과

• 부서 검색이 WHERE department = '디자인팀'으로 간단해짐
• 부서 추가/삭제가 INSERT/DELETE로 끝남
• UNIQUE 제약으로 같은 권한 중복 추가 방지

---

실습 2: 댓글 관리와 이력 추적

시나리오

“사용자가 정책에 댓글을 달 수 있다. 댓글은 수정/삭제할 수 있고, 모든 변경 이력을 추적한다.”

테이블 설계

왜 테이블을 2개로 나눴나?

comments 테이블: 현재 상태만 저장 (최신 버전)

• 화면에 보여줄 댓글 내용
• 빠른 조회 (최신 상태만 읽으면 됨)

comment_history 테이블: 모든 변경 기록 저장 (전체 이력)

• 누가 언제 무엇을 수정했는지
• 삭제된 댓글도 복구 가능
• 감사(audit) 목적으로 활용 가능

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

CREATE TABLE comment_history (
  history_id INTEGER PRIMARY KEY AUTOINCREMENT,
  comment_id INTEGER NOT NULL,
  content TEXT,
  modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  modified_by INTEGER NOT NULL,
  action TEXT CHECK(action IN ('created', 'updated', 'deleted')),
  FOREIGN KEY (comment_id) REFERENCES comments(comment_id),
  FOREIGN KEY (modified_by) REFERENCES users(user_id)
);

핵심 개념 설명

1. soft delete (소프트 삭제)

deleted_at TIMESTAMP 컬럼의 역할:

활성 댓글 (deleted_at IS NULL):

• comment_id=1: “좋은 정책입니다” (deleted_at: NULL)
• comment_id=2: “도움됐어요” (deleted_at: NULL)

삭제된 댓글 (deleted_at IS NOT NULL):

• comment_id=3: “애매한 정책이네요” (deleted_at: 2026-02-13 17:00)
  • 화면에는 comment_id=1, 2만 표시
  • comment_id=3은 DB에 유지되지만 숨김

왜 실제로 삭제(DELETE)하지 않나?

• 복구 가능성: 실수로 삭제해도 되돌릴 수 있음
• 이력 추적: “누가 언제 삭제했는지” 기록 남음
• 법적 요구사항: 일부 서비스는 삭제 기록 보관 의무

처음 시도했던 방식:

deleted BOOLEAN  -- true/false만 저장

문제점: “언제 삭제했는지” 알 수 없음

개선한 방식:

deleted_at TIMESTAMP  -- 삭제 시점까지 기록

2. CHECK 제약으로 허용값 제한

action TEXT CHECK(action IN ('created', 'updated', 'deleted'))

왜 필요한가?

제약 없이 두면 개발자가 임의로 이렇게 입력할 수 있음:

• ‘CREATED’ (대문자)
• ‘Update’ (첫 글자만 대문자)
• ‘modify’ (다른 단어)

CHECK 제약을 걸면:

• ‘created’, ‘updated’, ‘deleted’ 딱 3개 값만 허용
• 다른 값 입력 시 DB가 에러 발생: “CHECK constraint failed”
• 기획에서 정한 규칙을 DB 레벨에서도 지킬 수 있다는 게 신기했던 부분

3. modified_by로 수정자 추적

modified_by INTEGER,
FOREIGN KEY (modified_by) REFERENCES users(user_id)

왜 필요한가?

댓글을 쓴 사람(user_id)과 수정한 사람(modified_by)이 다를 수 있음:

• 일반적: 본인이 작성한 글을 본인이 수정 (user_id = modified_by)
• 특수한 경우: 다른 관리자가 삭제 (user_id ≠ modified_by)

이력 추적 예시:

• history_id=1: 댓글1, 김철수→김철수, created
• history_id=2: 댓글1, 김철수→김철수, updated (본인 수정)
• history_id=3: 댓글1, 김철수→관리자, deleted (관리자 삭제)

→ 원작성자와 수정자가 다를 수 있음을 기록

JOIN으로 의미 있는 정보 조회

comment_history만 조회하면:

• history_id=1: comment_id=1, modified_by=1, action=created
• history_id=2: comment_id=1, modified_by=1, action=updated
  • modified_by=1이 누군지 모름 (숫자만 보임)

users 테이블과 JOIN하면:

SELECT 
  h.history_id,
  h.comment_id AS 댓글번호,
  u_author.name AS 원작성자,
  u_modifier.name AS 수정자,
  h.action AS 행동,
  h.content AS 내용,
  h.modified_at AS 시간
FROM comment_history h
JOIN comments c ON h.comment_id = c.comment_id
JOIN users u_author ON c.user_id = u_author.user_id
JOIN users u_modifier ON h.modified_by = u_modifier.user_id
ORDER BY h.comment_id, h.modified_at;

결과:

• 댓글1: 원작성자=김철수, 수정자=김철수, created, “좋은 정책입니다”
• 댓글1: 원작성자=김철수, 수정자=김철수, updated, “개선 필요해요”
  • “김철수”라는 의미 있는 정보 획득

이 과정을 통해 FK와 JOIN의 필요성을 체감했습니다. FK로 연결하고 JOIN으로 조회하면, 단순한 숫자(user_id=1)가 의미 있는 정보(김철수)로 바뀔 수 있습니다.

숫자만 보이는 데이터를 의미 있는 정보로 바꾸는 것이 관계형 DB의 핵심입니다.

---

실습 3: 권한 관리

권한 관리는 3단계로 나눠 점진적으로 확장했습니다. 각 시나리오는 이전 시나리오의 한계를 해결합니다.

시나리오 1: 부서별 권한

요구사항:

• 인사팀 정책 → 전 부서 열람 가능
• 디자인팀 정책 → 디자인팀만 열람
• 마케팅팀 정책 → 마케팅팀 + 경영지원팀 열람

테이블 설계:

CREATE TABLE policy_permissions (
  permission_id INTEGER PRIMARY KEY AUTOINCREMENT,
  policy_id INTEGER NOT NULL,
  department TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (policy_id) REFERENCES policies(policy_id),
  UNIQUE(policy_id, department)
);

데이터 예시:

• 정책1 – 인사팀만 열람 가능
• 정책1 – 디자인팀만 열람 가능
• 정책1 – 마케팅팀만 열람 가능
• 정책2 – 디자인팀만 열람 가능
• 정책3 – 마케팅팀만 열람 가능
• 정책3 – 경영지원팀만 열람 가능

복합 UNIQUE(policy_id, department)의 역할:

• 정책1에 “디자인팀” 권한을 실수로 2번 추가하는 것 방지
• (1, 디자인팀) 조합은 단 한 번만 허용

왜 별도 테이블로 분리했나?

잘못된 방식:

CREATE TABLE policies (
  policy_id INTEGER PRIMARY KEY,
  title TEXT,
  accessible_departments TEXT  -- "디자인팀,마케팅팀,개발팀"
);

문제점:

1. 검색 느림: WHERE accessible_departments LIKE '%디자인팀%' (전체 스캔)
2. 부서 추가/삭제: 문자열 파싱 후 수정 필요
3. 부서별 권한 부여 시점 추적 불가
4. 통계 어려움: “디자인팀에게 권한 있는 정책 수” 집계 복잡

올바른 방식:

• 부서마다 1개 row
• 검색 빠름: WHERE department = '디자인팀' (인덱스 사용)
• 부서 추가: INSERT INTO policy_permissions...
• 부서 삭제: DELETE FROM policy_permissions...

---

시나리오 2: 직급별 권한 추가

요구사항:

• 경영 전략 정책 → 과장 이상만 열람
• 복리후생 정책 → 전 직원 열람
• 임원 회의록 → 임원만 열람

시나리오 1의 한계:

• 부서는 체크하는데 직급은 체크 못 함
• “디자인팀 과장 이상” 같은 조건 불가능

테이블 확장:

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  department TEXT,
  position TEXT,           -- "사원", "대리", "과장" (화면 표시용)
  position_level INTEGER,  -- 1, 2, 3 (권한 체크용)
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE policy_permissions (
  permission_id INTEGER PRIMARY KEY AUTOINCREMENT,
  policy_id INTEGER NOT NULL,
  department TEXT,              -- NULL 허용
  min_position_level INTEGER,   -- NULL 허용
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (policy_id) REFERENCES policies(policy_id),
  UNIQUE(policy_id, department, min_position_level)
);

핵심 개념 설명

1. position vs position_level 분리 이유

position (TEXT): “과장”, “차장”, “부장”

• 역할: 화면에 표시
• 사용처: 사용자 프로필, 댓글 작성자 표시

position_level (INTEGER): 3, 4, 5

• 역할: 권한 비교
• 사용처: “과장 이상” 조건 체크

문자열만 쓰면 안 되나?

문자열로 “과장 이상” 체크하려면:

if (user.position == "과장" || 
    user.position == "차장" || 
    user.position == "부장" || 
    user.position == "임원") {
  allow();
}

→ 직급 추가될 때마다 코드 수정

숫자로 체크하면:

if (user.position_level >= 3) {
  allow();
}

→ 직급 추가되어도 level만 부여하면 끝

이 부분에서 “왜 데이터 타입을 잘 설계해야 하는지” 이해가 됐습니다.

2. min_position_level의 의미

왜 “min” (최소)인가?

• “이상” 조건이기 때문

예시: min_position_level = 3 (과장)

• 허용: 과장(3), 차장(4), 부장(5), 임원(6) ✅
• 차단: 사원(1), 대리(2) ❌
• 체크 조건: user.position_level >= 3

실제 권한 체크 SQL:

WHERE user.position_level >= permissions.min_position_level

3. NULL 허용 컬럼의 의미

department NULL: 부서 제한 없음 = 전 부서 대상

권한 규칙 예시:

• 정책1: department=NULL, min_position_level=NULL → 전 부서, 전 직급
• 정책2: department=NULL, min_position_level=5 → 전 부서, 부장 이상
• 정책3: department=디자인팀, min_position_level=3 → 디자인팀, 과장 이상

예시 1: 임원 회의록

• 요구사항: “부서 상관없이 임원만”
• 데이터: department=NULL, min_position_level=6
• 의미: 모든 부서의 임원 허용

예시 2: 복리후생 정책

• 요구사항: “전 직원”
• 데이터: department=NULL, min_position_level=NULL
• 의미: 제한 없음

NULL 없이 하려면?

• 10개 부서면 10개 row 생성 필요
• 새 부서 추가될 때마다 row 추가
• 비효율적

---

시나리오 3: 개인 예외 처리

요구사항:

• 재무 보고서 → 재무팀 전체 + CEO (경영팀 소속이지만 예외 허용)
• 보안 정책 → 개발팀 전체 + 디자인팀 과장 이상 + 디자인팀 사원 이영희 (예외 허용)
• 보안 정책 → 개발팀 과장 최지원 (보안 위반으로 차단)

시나리오 2의 한계:

• 부서+직급 조건은 가능하지만 개인 예외는 불가능
• “디자인팀 사원은 허용 안 되지만 이영희만 허용” 불가능

최종 테이블:

CREATE TABLE policy_permissions (
  permission_id INTEGER PRIMARY KEY AUTOINCREMENT,
  policy_id INTEGER NOT NULL,
  permission_type TEXT CHECK(permission_type IN ('department', 'position', 'user_exception')),
  department TEXT,
  min_position_level INTEGER,
  user_id INTEGER,
  is_allowed INTEGER DEFAULT 1,  -- 1=허용, 0=차단
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (policy_id) REFERENCES policies(policy_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  UNIQUE(policy_id, permission_type, department, min_position_level, user_id)
);

핵심 개념 설명

1. permission_type으로 조건 구분

왜 필요한가?

한 테이블에 3가지 종류의 규칙을 저장하기 위해:

permission_type = 'department'      → 부서 조건 (재무팀 전체)
permission_type = 'position'            → 직급 조건 (과장 이상)
permission_type = 'user_exception' → 개인 예외 (CEO만 특별히)

데이터 예시 (정책3: 보안 정책):

• permission_id=1: type=department, 개발팀 전체, is_allowed=1
• permission_id=2: type=department, 디자인팀 과장(3) 이상, is_allowed=1
• permission_id=3: type=user_exception, 이영희(user_id=2), is_allowed=1
• permission_id=4: type=user_exception, 최지원(user_id=4), is_allowed=0

테이블을 분리하지 않은 이유:

❌ 이렇게 하면 관리가 복잡해짐:

Table department_permissions { ... }  -- 부서용
Table position_permissions { ... }       -- 직급용
Table user_exceptions { ... }              -- 예외용

문제점:

• 테이블 3개 관리
• 권한 체크 시 JOIN 3번
• 새 조건 타입 추가 시 테이블 추가

✅ 한 테이블에 type으로 구분:

WHERE permission_type IN ('department', 'user_exception')

→ 한 번에 조회, 확장 쉬움

2. user_id로 개인 지정

user_id INTEGER,
FOREIGN KEY (user_id) REFERENCES users(user_id)

언제 사용하나?

• permission_type = 'user_exception'일 때만
• 나머지 경우는 NULL

예시:

• CEO(user_id=5)에게 재무 보고서 특별 허용
• 보안 위반자(user_id=4)를 보안 정책에서 차단

3. is_allowed로 허용/차단 구분

is_allowed INTEGER DEFAULT 1  -- 1=허용, 0=차단

왜 필요한가?

대부분은 “허용” 규칙이지만, 때로는 “차단” 규칙도 필요합니다.

예시: 정책3 (보안 정책)

• 개발팀 전체 허용 (is_allowed=1)
• 디자인팀 과장 이상 허용 (is_allowed=1)
• 이영희 개인 허용 (is_allowed=1)
• 최지원 개인 차단 (is_allowed=0) ← 차단 규칙

DEFAULT 1의 의미:

• 값을 생략하면 자동으로 1
• 대부분 허용 규칙이므로 편의성 향상

4. 5개 컬럼 복합 UNIQUE

UNIQUE(policy_id, permission_type, department, min_position_level, user_id)

왜 5개나 포함되는가?

같은 규칙 중복 방지:

✅ 허용 (다른 조합):

• (정책3, department, 개발팀, NULL, NULL) – 개발팀 전체
• (정책3, department, 디자인팀, 3, NULL) – 디자인팀 과장 이상
• (정책3, user_exception, NULL, NULL, 2) – 이영희 개인

❌ 차단 (중복):

• (정책3, department, 개발팀, NULL, NULL) – 첫 번째 등록
• (정책3, department, 개발팀, NULL, NULL) – 중복! DB 에러 발생

SQLite의 NULL 특성:

• NULL은 UNIQUE 비교에서 특수하게 처리됨
• (1, department, 개발팀, NULL, NULL)과 (1, department, 개발팀, NULL, NULL)은 중복으로 간주
• 하지만 (1, user_exception, NULL, NULL, 2)와 (1, user_exception, NULL, NULL, 3)은 다름 (user_id가 다름)

---

특정 사용자 권한 체크

디자인팀 사원 이영희(user_id=2)가 볼 수 있는 정책:

SELECT DISTINCT
  p.policy_id,
  p.title AS 정책명
FROM policies p
JOIN policy_permissions pp ON p.policy_id = pp.policy_id
WHERE 
  -- 일반 규칙 (부서 + 직급)
  (pp.permission_type IN ('department', 'position')
   AND pp.is_allowed = 1
   AND (pp.department IS NULL OR pp.department = '디자인팀')
   AND (pp.min_position_level IS NULL OR 1 >= pp.min_position_level))
  OR
  -- 개인 예외 (허용)
  (pp.permission_type = 'user_exception' 
   AND pp.user_id = 2 
   AND pp.is_allowed = 1)
ORDER BY p.policy_id;

쿼리 해석:

1. 일반 규칙 체크:

pp.permission_type IN ('department', 'position')  -- 부서나 직급 규칙
AND pp.is_allowed = 1                             -- 허용 규칙만
AND (pp.department IS NULL OR pp.department = '디자인팀')  -- 전체 또는 디자인팀
AND (pp.min_position_level IS NULL OR 1 >= pp.min_position_level)  -- 전체 또는 사원(1) 이상

2. 개인 예외 체크:

pp.permission_type = 'user_exception'  -- 개인 예외 규칙
AND pp.user_id = 2                     -- 이영희
AND pp.is_allowed = 1                  -- 허용

3. OR 연결:

• 일반 규칙 또는 개인 예외 중 하나만 맞아도 열람 가능

권한 로직의 핵심:

• 여러 조건을 OR로 연결
• 부서 OR 직급 OR 개인예외 중 하나라도 맞으면 허용
• 이 부분을 만약 if문으로 표현한다면 코드 100줄 필요, 테이블로 하면 데이터 몇 줄로 해결 가능

bookmarks TEXT  -- "1,3,5"

CREATE TABLE bookmarks (user_id, policy_id, ...)

updated_at TIMESTAMP  -- 아직 수정 안 함 → NULL

department TEXT  -- 전 부서 → NULL
min_position_level INTEGER  -- 전 직급 → NULL

INSERT INTO policy_permissions (policy_id, department) VALUES (1, '디자인팀');
INSERT INTO policy_permissions (policy_id, department) VALUES (1, '디자인팀');  -- 중복!

Error: UNIQUE constraint failed: policy_permissions.policy_id, policy_permissions.department

deleted BOOLEAN  -- true/false

deleted_at TIMESTAMP  -- 2026-02-13 17:00:00