컴퓨터공학 300 주제 시리즈의 164번째 글이다. 전체 지도는 여기.

한 줄 요약

ER(Entity-Relationship) 모델링은 업무 세계를 개체, 속성, 관계로 그려 놓고, 그 그림을 정해진 규칙에 따라 테이블로 옮기는 설계 방법이다.

왜 필요한가

테이블부터 만들면 대개 두 가지 일이 생긴다. 하나는 같은 정보가 여러 테이블에 흩어져 서로 어긋나는 것이고, 다른 하나는 “한 학생이 여러 강의를 듣는다”는 사실을 담을 자리가 없어 콤마로 이어 붙인 문자열 열이 생기는 것이다. 둘 다 나중에 고치기 비싸다. 데이터는 코드보다 오래 살기 때문이다.

Peter Chen 은 1976년 논문 “The Entity-Relationship Model — Toward a Unified View of Data” 에서 데이터를 개체와 관계로 바라보는 표기법을 제안했다. 이 그림은 개발자와 기획자가 같이 볼 수 있을 만큼 단순하면서도, 테이블로 옮기는 규칙이 기계적으로 정해져 있다. 그래서 지금도 설계 회의의 공용어로 쓰인다.

핵심 개념

세 가지 구성 요소

요소 뜻 예 테이블로 가면
개체 (entity) 독립적으로 식별되는 대상 학생, 강의 테이블
속성 (attribute) 개체나 관계의 성질 이름, 학점 열
관계 (relationship) 개체 사이의 연관 수강한다 외래 키 또는 연결 테이블

개체 하나하나는 개체 인스턴스, 같은 종류의 모음은 개체 집합이다. 이 구분은 테이블과 행의 구분과 같다.

속성의 종류

  • 키 속성: 개체를 식별한다(학번).
  • 복합 속성: 더 쪼갤 수 있다(주소 = 시·구·도로명). 질의에 쓰는 단위까지 쪼개 열로 둔다.
  • 다중값 속성: 값이 여러 개다(전화번호들). 별도 테이블로 뺀다.
  • 유도 속성: 다른 값에서 계산된다(나이 = 오늘 − 생일). 보통 저장하지 않는다.

카디널리티

관계 양쪽에 “몇 개와 연결될 수 있는가”를 적는다. 표기법은 여럿이지만 실무에서는 까마귀발(crow’s foot) 표기가 가장 흔하다.

학과 ||──────o<  학생        1:N   한 학과에 학생 0명 이상, 학생은 학과 정확히 1개
학생 >o──────o<  강의        M:N   학생은 강의 0개 이상, 강의도 학생 0명 이상
사원 ||──────o|  사원증      1:1   사원은 사원증 0~1개

||  정확히 하나     o|  0 또는 1
|<  하나 이상       o<  0 이상

바깥쪽 기호가 최대(하나/여럿), 안쪽 기호가 최소(0이면 선택, 1이면 필수)다. 최소값이 1인 쪽을 전체 참여라 하며, 테이블에서는 NOT NULL 외래 키로 나타난다.

약한 개체

스스로는 식별되지 않고 다른 개체(소유 개체)에 기대어 식별되는 개체다. “학생 1번의 2번째 연락처”처럼 소유자의 키와 자신의 부분 키를 합쳐야 유일해진다. 소유자가 사라지면 함께 사라지는 게 자연스러우므로 ON DELETE CASCADE 와 짝을 이룬다.

테이블로 옮기는 규칙

ER 요소 변환
강한 개체 테이블 하나, 키 속성이 기본 키
약한 개체 테이블 하나, 기본 키 = 소유자 키 + 부분 키
1:N 관계 N 쪽 테이블에 1 쪽 키를 외래 키로
1:1 관계 한쪽(보통 선택 참여 쪽)에 외래 키 + UNIQUE
M:N 관계 연결 테이블, 기본 키 = 양쪽 외래 키 묶음
다중값 속성 별도 테이블, 기본 키 = 소유자 키 + 값
관계의 속성 관계를 담는 곳(외래 키 쪽 또는 연결 테이블)에 열로

M:N 이 핵심이다. 관계형 모델의 칸에는 원자값 하나만 들어가므로 “여러 강의”를 한 칸에 넣을 수 없다. 그래서 관계 자체를 테이블로 만든다. 성적처럼 “학생과 강의의 조합”에 붙는 속성은 학생에도 강의에도 둘 수 없고 연결 테이블에만 자리가 있다. ER 다이어그램에서 관계에 속성을 그리는 이유가 이것이다.

개념 → 논리 → 물리

설계는 보통 세 단계로 내려간다.

  1. 개념 모델: ER 다이어그램. DBMS 와 무관하다.
  2. 논리 모델: 테이블, 열, 키, 외래 키. 정규화를 여기서 한다.
  3. 물리 모델: 타입, 인덱스, 파티션, 저장 옵션. 특정 DBMS 에 맞춘다.

단계를 나누는 이유는 질문을 분리하기 위해서다. “학생은 학과를 여러 개 가질 수 있나?”는 업무 질문이고, “이 열에 인덱스를 둘까?”는 성능 질문이다. 둘을 한 번에 고민하면 둘 다 흐려진다.

식별자 선택: 자연 키와 대리 키

학번, 주민번호 같은 자연 키는 의미가 있지만 바뀔 수 있고, 개인정보일 수 있다. 의미 없는 일련번호나 UUID 인 대리 키는 바뀌지 않는다. 실무에서는 대리 키를 기본 키로 쓰고, 자연 키에는 UNIQUE 제약을 따로 거는 방식이 흔하다. 그래야 자연 키가 바뀌어도 그 값을 참조하는 모든 외래 키를 고칠 필요가 없다.

직접 해 보기

학생–강의(M:N, 관계 속성 성적)와 학생–연락처(약한 개체)를 테이블로 옮겨 본다.

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON")
con.executescript("""
-- 개체: 학생, 강의
CREATE TABLE student (student_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE course  (course_id  TEXT PRIMARY KEY,    title TEXT NOT NULL);
-- M:N 관계 '수강' → 연결 테이블. 관계의 속성(성적)도 여기에 둔다
CREATE TABLE enrollment (
  student_id INTEGER REFERENCES student(student_id) ON DELETE CASCADE,
  course_id  TEXT    REFERENCES course(course_id),
  grade      TEXT,
  PRIMARY KEY (student_id, course_id)
);
-- 약한 개체: 학생의 연락처 (학생 없이 존재 못 함)
CREATE TABLE contact (
  student_id INTEGER REFERENCES student(student_id) ON DELETE CASCADE,
  seq        INTEGER,
  phone      TEXT NOT NULL,
  PRIMARY KEY (student_id, seq)
);
INSERT INTO student VALUES (1,'가람'),(2,'나래');
INSERT INTO course  VALUES ('CS101','자료구조'),('CS102','데이터베이스');
INSERT INTO enrollment VALUES (1,'CS101','A'),(1,'CS102','B'),(2,'CS102','A');
INSERT INTO contact VALUES (1,1,'010-0000-0001'),(1,2,'010-0000-0002');
""")
for r in con.execute("""SELECT s.name, c.title, e.grade FROM enrollment e
    JOIN student s USING (student_id) JOIN course c USING (course_id)
    ORDER BY s.student_id, c.course_id"""):
    print(r)
try:
    con.execute("INSERT INTO enrollment VALUES (1,'CS101','C')")
except sqlite3.IntegrityError as e:
    print("중복 수강 거부:", e)
con.execute("DELETE FROM student WHERE student_id = 1")
print("남은 수강:", con.execute("SELECT COUNT(*) FROM enrollment").fetchone()[0],
      "남은 연락처:", con.execute("SELECT COUNT(*) FROM contact").fetchone()[0])

결과:

('가람', '자료구조', 'A')
('가람', '데이터베이스', 'B')
('나래', '데이터베이스', 'A')
중복 수강 거부: UNIQUE constraint failed: enrollment.student_id, enrollment.course_id
남은 수강: 1 남은 연락처: 0

연결 테이블의 복합 기본 키가 “같은 학생이 같은 강의를 두 번 수강”하는 것을 막았다. 학생 1번을 지우자 그의 수강 두 건과 연락처 두 건이 CASCADE 로 함께 사라졌다. 연락처는 약한 개체라 그게 맞다. 반면 수강 기록까지 지워도 되는지는 업무 규칙에 달려 있다. 성적 이력을 보존해야 한다면 CASCADE 대신 RESTRICT 로 삭제 자체를 막고, 학생은 “탈퇴” 상태로 표시하는 편이 낫다. 이런 결정이 ER 단계에서 나와야 한다.

현업에서는

  • “이거 1:N 맞아요?” 질문이 설계 리뷰의 절반이다. 처음엔 “주문당 배송지 하나”였다가 분할 배송이 생기면서 1:N 이 되는 식이다. 카디널리티가 바뀌면 테이블 구조가 바뀌므로, 초기에 기획자와 확인할 가치가 크다.
  • M:N 연결 테이블은 결국 개체가 된다. 처음엔 (user_id, group_id) 뿐이던 멤버십 테이블에 가입일, 역할, 초대자가 붙는다. 그때 대리 키를 추가할지, 복합 키를 유지할지 결정하게 된다.
  • 도구: dbdiagram 류의 DSL 도구나 ERD 를 DB 에서 역으로 그려 주는 도구가 흔하다. 다만 그림은 결과일 뿐이고, 진짜 산출물은 카디널리티와 필수 여부에 대한 합의다.
  • 삭제 정책: CASCADE 를 무심코 걸었다가 상위 레코드 하나를 지운 것이 수만 행 삭제로 번지는 사고가 있다. 소유 관계(약한 개체)에만 CASCADE, 나머지는 RESTRICT 가 안전한 기본값이다.

확인 문제

  1. “직원은 여러 프로젝트에 참여하고, 참여할 때마다 역할이 있다.” 이 관계를 테이블로 옮기면 역할 열은 어디에 두어야 하는가?
  2. 1:N 관계에서 외래 키를 1 쪽에 두면 어떤 문제가 생기는가?
  3. 약한 개체의 기본 키는 어떻게 구성되는가? 예를 들어라.
  4. 학번을 기본 키로 쓰는 대신 대리 키를 쓰자는 주장의 근거 두 가지를 들어라.

풀이

  1. 직원–프로젝트는 M:N 이므로 연결 테이블 assignment(emp_id, project_id, role) 을 만들고 역할은 거기에 둔다. 역할은 직원 하나나 프로젝트 하나가 아니라 둘의 조합에 붙는 속성이다.
  2. 1 쪽 행 하나가 여러 N 쪽 값을 가져야 하므로 한 칸에 여러 값을 넣거나 행을 복제해야 한다. 원자값 규칙이 깨지고 갱신 이상이 생긴다.
  3. 소유 개체의 키 + 부분 키. 예: 주문 품목 order_item(order_id, line_no).
  4. 자연 키는 바뀔 수 있어 참조하는 모든 외래 키를 갱신해야 한다. 개인정보가 키로 퍼지면 여러 테이블과 로그에 노출된다. (그 밖에 길이가 짧고 일정해 인덱스가 작다는 점도 있다.)

더 읽을거리 (References)

  • Peter Pin-Shan Chen, “The Entity-Relationship Model — Toward a Unified View of Data”, ACM Transactions on Database Systems, 1(1), 1976.
  • Abraham Silberschatz, Henry F. Korth, S. Sudarshan, Database System Concepts, 7th ed., McGraw-Hill, 2019. 6장 “Database Design Using the E-R Model”
  • PostgreSQL 공식 문서, Constraints — 외래 키의 ON DELETE 동작
  • SQLite 공식 문서, SQLite Foreign Key Support