ER 모델링 - 요구사항에서 개체와 관계 찾기

이 장에서 배우는 것

앞 장에서는 이미 만들어진 테이블에 기본키·외래키·CHECK 제약을 걸어 틀린 데이터를 막는 법을 다뤘다. 그런데 그 테이블은 애초에 어떻게 정해졌을까. 사업팀이 던진 문장 몇 개에서 member, orders, book_author 같은 표와 그 사이의 관계를 뽑아내는 과정이 있어야 테이블 설계가 시작된다. 이 장에서는 그 과정, 즉 ER 모델링(entity-relationship modeling)을 다룬다. 표를 그리기 전에 개체와 관계를 종이 위에 먼저 정리해 두면, 뒤늦게 "저자가 여러 명인 책도 있던데요" 같은 말을 듣고 테이블을 다시 짜는 일을 줄일 수 있다.

• 요구사항 문장에서 개체(entity)·속성(attribute)·관계(relationship)를 구분해 낼 수 있다
• 1:1, 1:N, M:N 카디널리티(cardinality)와 전체/부분 참여도(participation)를 읽고 그릴 수 있다
• 약한 개체(weak entity)와 식별 관계, 자기참조(재귀) 관계를 알아본다
• 서점 도메인의 실제 데이터를 조회해 ERD에서 정한 카디널리티가 맞는지 검증할 수 있다
• Chen 표기법과 까마귀발(crow's foot) 표기법의 차이를 비교할 수 있다

문제 상황

SQL 연구소에 새로 들어온 개발자가 사업팀에서 받은 요구사항 문서를 펼친다. "회원은 책을 주문한다. 책에는 저자가 있다. 주문하면 결제하고 배송한다." 문장이 짧아 보여서 바로 CREATE TABLE부터 쓰기 시작한다. member 표와 book 표를 만들고, 주문 정보는 member와 book을 잇는 표 하나에 주문일시와 상태만 넣어 끝낸다.

일주일 뒤 사업팀이 새로운 요구사항을 들고 온다. "책 한 권에 저자가 여러 명일 수 있고, 저자 한 명이 여러 책을 쓰기도 해요." "주문 하나에 책을 여러 권 담을 수 있어야죠." "카드로 못 채우면 포인트로 나눠 결제하기도 해요." 처음 만든 표로는 이 요구사항 중 어느 하나도 제대로 담을 수 없다. 표부터 그린 것이 문제가 아니라, 표를 그리기 전에 개체가 몇 개이고 그 사이 관계가 1:N인지 M:N인지를 먼저 정리하지 않은 것이 문제다. ER 모델링은 이 정리 작업을 요구사항 문장과 실제 테이블 설계 사이에 끼워 넣는 단계다.

개체·속성·관계 구분하기

개체(entity)는 데이터베이스가 독립적으로 식별해 추적해야 하는 대상이다. 요구사항 문장에서는 대개 명사로 나타난다. 서점 도메인에서는 회원(member), 저자(author), 출판사(publisher), 카테고리(category), 도서(book), 쿠폰(coupon)이 눈에 바로 띄는 개체다.

속성(attribute)은 개체가 갖는 성질 값이다. book이라는 개체는 title, price, published_on, pages 같은 속성을 갖는다. member는 email, name, grade, region, joined_on을 갖는다. 속성 하나하나는 그 자체로 독립된 데이터가 아니라 개체에 딸려 있는 값이라는 점이 개체와의 차이다.

관계(relationship)는 개체 사이의 연결이다. "회원이 책을 주문한다"는 문장은 얼핏 회원과 도서 사이의 단순한 관계처럼 보인다. 하지만 주문에는 주문일시, 상태(PAID/SHIPPED/DELIVERED/CANCELLED/REFUNDED), 결제 수단처럼 관계 자체가 갖는 데이터가 많다. 이렇게 관계에 속성이 쌓이면 그 관계를 독립된 개체로 승격시킨다. 그 결과가 orders다. 같은 이유로 payment, shipment, review도 처음에는 관계처럼 보이지만 결국 자기만의 속성과 생명주기를 가진 개체로 다룬다.

정리하면 서점 도메인의 개체는 member, author, publisher, category, book, inventory, coupon, orders, order_item, payment, shipment, review이고, book과 author 사이의 M:N 관계는 book_author라는 연결 개체(associative entity)로 풀어낸다.

카디널리티와 참여도

카디널리티(cardinality)는 관계 양쪽에서 상대 개체를 최대 몇 개까지 가리킬 수 있는지를 정한다. 1:1은 양쪽 다 하나씩만 연결되는 경우, 1:N은 한쪽은 하나인데 반대쪽은 여러 개인 경우, M:N은 양쪽 다 여러 개일 수 있는 경우다.

카디널리티는 관계 양쪽의 최대 개수를 정한다

카디널리티	서점 예시	읽는 법	비고
1:1	book - inventory	책 한 권에 재고 행 하나	창고가 여러 곳이면 1:N으로 바뀔 수 있음
1:N	member - orders	회원 한 명이 주문 여러 건	주문 하나는 항상 회원 한 명에만 속함
1:N	category(상위) - category(하위)	상위 카테고리 하나에 하위 여러 개	같은 개체끼리 맺는 재귀 관계
M:N	book - author	책 한 권에 저자 여러 명, 저자 한 명이 책 여러 권	book_author로 풀어서 저장

참여도(participation)는 카디널리티와 다른 질문에 답한다. "이 개체의 모든 행이 반드시 저 관계에 참여해야 하는가"이다. 반드시 참여해야 하면 전체(total, 필수) 참여, 참여하지 않는 행이 있어도 되면 부분(partial, 선택) 참여라고 부른다. 주문은 반드시 회원 한 명에 속해야 하므로 orders 쪽에서 member로의 참여는 전체다. 반대로 아직 한 번도 주문하지 않은 회원도 있을 수 있으므로 member 쪽에서 orders로의 참여는 부분이다.

참여도는 관계에 반드시 참여해야 하는지를 정한다

개체	관계 상대	참여도	의미
orders	member	전체(필수)	주문에는 항상 회원이 있어야 함
member	orders	부분(선택)	주문한 적 없는 회원도 있을 수 있음
orders	coupon	부분(선택)	쿠폰 없이 주문할 수 있음(coupon_id NULL)
order_item	orders	전체(필수), 식별	주문 없이 주문항목은 존재할 수 없음

아래 그림은 회원부터 저자까지 이어지는 관계망을 카디널리티와 함께 보여 준다. 주문항목(order_item)은 다음 절에서 다룰 약한 개체다.

약한 개체와 재귀 관계

order_item을 다시 본다. line_no는 한 주문 안에서만 유일하다. 주문 1001의 1번째 줄과 주문 1002의 1번째 줄은 line_no 값이 똑같이 1이어도 서로 다른 행이다. 이렇게 스스로는 유일한 키를 갖지 못하고 다른 개체(여기서는 orders)의 키를 빌려야 완전히 식별되는 개체를 약한 개체(weak entity)라고 부른다. order_item을 orders에 종속시키는 관계는 식별 관계(identifying relationship)라고 부르며, Chen 표기법에서는 이중선 사각형과 이중선 마름모로 표시해 일반 관계와 구분한다.

category의 parent_id는 다른 성격의 관계를 만든다. category가 가리키는 상대 개체도 category 자신이다. 이런 관계를 재귀 관계(recursive relationship) 또는 자기참조 관계라고 부른다. 카디널리티는 1:N이며, 상위 카테고리 하나에 하위 카테고리가 여러 개 달릴 수 있다는 뜻이다. 이 책의 서점 데이터는 이 관계를 2단계까지만 쓴다. 소설이라는 상위 카테고리 아래 한국소설, 외국소설이라는 하위 카테고리가 있는 식이다.

표기법은 책이나 도구마다 다르다. 관계를 마름모로 그리는 Chen 표기법도 있고, 선 끝에 기호만 붙이는 까마귀발 표기법도 있다. 어느 쪽을 쓰든 담아야 하는 정보는 같다.

표기법마다 개체와 관계를 그리는 방식이 다르다

표기법	개체 표시	관계 표시	카디널리티 표시
Chen	사각형	마름모(동사로 이름 붙임)	마름모 옆 숫자(1, N, M)
까마귀발(Crow's Foot)	사각형	사각형을 잇는 선	선 끝의 까마귀발·막대 기호
UML 클래스 다이어그램	사각형(클래스)	선 또는 라벨	선 끝의 "0..1", "1..*" 같은 범위

ERD를 다 그렸다고 끝이 아니다. 그림 위의 카디널리티가 실제 데이터와 맞는지는 SQL 연구소의 데이터를 직접 조회해 확인할 수 있다. 아래 완성 코드는 이 장에서 다룬 네 가지 관계의 카디널리티를 실제로 검증하는 쿼리 묶음이다.

완성 코드

SQLite 콘솔에서 ch04_cardinality_check.sql로 저장해 그대로 실행할 수 있는 쿼리다.

-- (1) member : orders 가 1:N 인지 확인
-- 회원 한 명이 여러 건의 주문을 낼 수 있는지 본다
SELECT member_id, COUNT(*) AS order_count
FROM orders
GROUP BY member_id
ORDER BY order_count DESC
LIMIT 5;

-- (2) book : author 가 M:N 인지 확인
-- 저자가 둘 이상인 책이 있는지 본다
SELECT book_id, COUNT(*) AS author_count
FROM book_author
GROUP BY book_id
HAVING COUNT(*) > 1
LIMIT 5;

-- 책이 둘 이상인 저자가 있는지도 본다
SELECT author_id, COUNT(*) AS book_count
FROM book_author
GROUP BY author_id
HAVING COUNT(*) > 1
LIMIT 5;

-- (3) book : inventory 가 1:1 인지 확인
-- 재고 행이 책 하나당 정확히 하나인지 본다
SELECT book_id, COUNT(*) AS row_count
FROM inventory
GROUP BY book_id
HAVING COUNT(*) <> 1;

-- (4) orders : coupon 참여도 확인
-- 쿠폰 없이 주문한 건수와 쿠폰을 쓴 건수를 나눠 본다
SELECT
  SUM(CASE WHEN coupon_id IS NULL THEN 1 ELSE 0 END) AS no_coupon,
  SUM(CASE WHEN coupon_id IS NOT NULL THEN 1 ELSE 0 END) AS with_coupon
FROM orders;

줄별 해설

쿼리 (1)은 orders를 member_id로 묶어 각 회원의 주문 건수를 센다.

• GROUP BY member_id는 같은 회원의 주문 행을 하나로 묶는다.
• COUNT(*)가 2 이상인 회원이 있으면 member : orders가 1:N이라는 ERD의 판단이 데이터로도 확인된 것이다.
• ORDER BY order_count DESC는 주문을 가장 많이 낸 회원부터 보여 주도록 정렬한다.

쿼리 (2)는 book_author를 book_id, author_id 각각으로 묶어 카디널리티가 M:N인지 양방향으로 확인한다.

• 첫 번째 SELECT의 HAVING COUNT(*) > 1은 저자가 둘 이상인 책만 남긴다.
• 두 번째 SELECT의 HAVING COUNT(*) > 1은 책이 둘 이상인 저자만 남긴다.
• 두 결과가 모두 행을 반환하면 book과 author는 한쪽 방향만이 아니라 양쪽 다 여러 개를 가리키는 M:N 관계다.

쿼리 (3)은 inventory를 book_id로 묶어 1:1을 검증한다.

• HAVING COUNT(*) <> 1은 재고 행이 0개이거나 2개 이상인 책, 즉 1:1이 깨진 책만 남긴다.
• 이 쿼리 결과가 비어 있어야 book : inventory가 1:1이라는 설계가 실제 데이터와 맞는다.

쿼리 (4)는 orders.coupon_id의 참여도를 확인한다.

• CASE WHEN coupon_id IS NULL THEN 1 ELSE 0 END은 쿠폰 없는 주문이면 1, 있으면 0을 매긴다.
• 두 SUM을 나란히 두면 쿠폰 없는 주문이 실제로 존재하는지, 즉 orders : coupon 참여가 부분(선택)이라는 설계가 맞는지 한 번에 확인된다.

실행 결과

예시 결과다. 실제 값은 SQL 연구소에 채워진 데이터에 따라 달라진다.

sqlite3 bookstore.db < ch04_cardinality_check.sql

-- (1) 결과
member_id | order_count
----------|------------
12        | 7
31        | 5
4         | 4

-- (2) 결과 (저자가 여러 명인 책)
book_id | author_count
--------|-------------
205     | 2
318     | 3

-- (2) 결과 (책이 여러 권인 저자)
author_id | book_count
----------|-----------
9         | 4
21        | 2

-- (3) 결과
(결과 없음 — 모든 책의 재고 행이 정확히 1개)

-- (4) 결과
no_coupon | with_coupon
----------|------------
842       | 158

실무에서 자주 틀리는 것

관계에 속성이 쌓이는데도 개체로 승격하지 않기

주문을 그냥 "회원이 책을 주문하는 관계"로만 보고 member와 book을 잇는 표 하나에 모든 것을 우겨넣으면 주문 하나에 책 여러 권을 담을 수도, 결제·배송 정보를 붙일 곳도 없어진다.

-- 틀린 설계: 관계 표 하나에 모든 것을 넣으려 함
CREATE TABLE order_link (
  member_id INTEGER,
  book_id   INTEGER,
  ordered_at TEXT,
  status     TEXT
);
-- 문제: 한 주문에 책을 두 권 담으면 행이 두 개로 쪼개지고
-- 결제·배송 정보를 붙일 독립된 대상이 없다

-- 고친 설계: 주문을 독립 개체로 승격하고 담긴 책은 order_item으로 분리
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL,
  ordered_at TEXT NOT NULL,
  status TEXT NOT NULL,
  coupon_id INTEGER
);
CREATE TABLE order_item (
  order_id INTEGER NOT NULL,
  line_no  INTEGER NOT NULL,
  book_id  INTEGER NOT NULL,
  qty      INTEGER NOT NULL,
  unit_price INTEGER NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

M:N을 목록 컬럼 하나에 우겨넣기

책 한 권에 저자가 여러 명이라는 사실을 알고도, book 표에 저자 이름을 콤마로 나열해 저장하는 경우가 있다.

-- 틀린 설계
CREATE TABLE book (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author_names TEXT  -- '김작가,이작가'
);

-- 고친 설계: 연결 개체로 분리하고 역할 속성을 둔다
CREATE TABLE book_author (
  book_id   INTEGER NOT NULL,
  author_id INTEGER NOT NULL,
  role      TEXT NOT NULL,
  PRIMARY KEY (book_id, author_id, role)
);

약한 개체의 키를 부분키만으로 잡기

order_item의 기본키를 line_no 하나로만 잡으면 서로 다른 주문의 1번째 줄끼리 기본키가 충돌한다.

-- 틀린 설계
CREATE TABLE order_item (
  line_no INTEGER PRIMARY KEY,
  order_id INTEGER,
  book_id  INTEGER
);
-- 문제: 주문 1001의 1번 줄과 주문 1002의 1번 줄이 같은 키로 충돌

-- 고친 설계: 부모 개체의 키를 합쳐 복합키로 식별
CREATE TABLE order_item (
  order_id INTEGER NOT NULL,
  line_no  INTEGER NOT NULL,
  book_id  INTEGER NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

선택 참여를 필수로 잘못 모델링하기

orders와 coupon의 참여도는 부분(선택)인데 이를 놓치고 NOT NULL로 걸면 쿠폰 없는 주문을 아예 넣을 수 없다.

-- 틀린 설계
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL,
  coupon_id INTEGER NOT NULL  -- 쿠폰 없는 주문을 막아 버림
);

-- 고친 설계: 참여도가 부분이면 NULL을 허용한다
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL,
  coupon_id INTEGER
);

한눈에 보기

이 장에서 다룬 개념을 한 줄로 정리한다

개념	정의	서점 예시
개체(entity)	독립적으로 식별해 추적하는 대상	member, book, orders
속성(attribute)	개체가 갖는 성질 값	book.title, member.grade
관계(relationship)	개체 사이의 연결	orders가 member와 book을 잇는다
카디널리티	관계 양쪽의 최대 개수	도서-저자는 M:N
참여도	관계에 반드시 참여해야 하는지	orders-member는 전체, member-orders는 부분
약한 개체	스스로 유일한 키가 없는 개체	order_item(line_no는 order_id 안에서만 유일)
재귀 관계	같은 개체 유형끼리 맺는 관계	category.parent_id

연습 문제 — SQL 연구소에서 실습하기

다음 과제는 정답과 해설 절에서 확인한다.

1. member, review, book 사이의 관계를 ER 관점에서 설명해 본다. review가 왜 단순 연결 표가 아니라 개체로 다뤄야 하는지 근거를 들어 본다.
2. payment와 orders의 카디널리티가 1:1인지 1:N인지 실제 데이터로 확인하는 SQL을 작성해 본다.
3. shipment와 orders에 대해서도 같은 방식으로 카디널리티를 확인하는 SQL을 작성하고, 결과가 무엇을 뜻하는지 써 본다.
4. category가 3단계 계층까지 지원해야 한다면 ERD의 재귀 관계 자체가 바뀌어야 하는지 서술해 본다.

정답과 해설

1. member와 book은 review를 통해 M:N 관계를 맺는다. 한 회원이 여러 책에 리뷰를 남길 수 있고, 한 책에 여러 회원이 리뷰를 남길 수 있기 때문이다. review는 rating, body, created_at이라는 자기만의 속성을 가지므로 book_author처럼 속성 없는 연결 표가 아니라 독립된 개체로 다룬다.

2.

SELECT order_id, COUNT(*) AS payment_count
FROM payment
GROUP BY order_id
HAVING COUNT(*) > 1;

이 쿼리가 행을 반환하면 한 주문에 결제가 여러 번 나뉘어 기록될 수 있다는 뜻이므로 orders : payment는 1:N이다. 결과가 비어 있으면 지금까지의 데이터로는 1:1에 가깝다고 볼 수 있다.

3.

SELECT order_id, COUNT(*) AS shipment_count
FROM shipment
GROUP BY order_id
HAVING COUNT(*) > 1;

결과가 비어 있다면 주문 하나에 배송 기록이 하나씩만 붙는다는 뜻이므로 orders : shipment는 1:1(또는 배송 전 상태를 고려하면 0:1)로 볼 수 있다.

4. 재귀 관계의 구조, 즉 category가 parent_id로 자기 자신을 1:N으로 참조한다는 사실은 바뀌지 않는다. 단계 수를 2단계로 제한할지 3단계로 늘릴지는 ERD가 표현하는 관계의 형태가 아니라 애플리케이션 로직이나 CHECK 제약 같은 별도 규칙으로 강제하는 문제다. ERD는 "카테고리는 자기 자신을 가리킬 수 있다"까지만 표현한다.