카테고리 없음

데이터 모델링 핵심 정리 | 정규화, 외래키, ERD, PostgreSQL 설계

boradora 2026. 7. 30. 08:48

작업일2026. 07

데이터 모델링 외래키 정규화 교차 테이블 ERD PostgreSQL

SQL 문법은 비교적 금방 익힐 수 있습니다. 하지만 테이블을 어디까지 나눌지, 부모를 삭제했을 때 자식 데이터를 어떻게 처리할지, 컬럼 타입을 무엇으로 선택할지 같은 설계는 훨씬 오래 고민하게 됩니다. 데이터가 쌓인 뒤에는 바꾸기 어렵기 때문이죠.

고객을 삭제하면 그 고객의 주문은 어떻게 되어야 할까요?
같이 지울지, 삭제를 막을지, 주문은 남기고 고객만 비울지를 설계할 때 정해 둡니다.

CREATE TABLE 문법과 SELECT 기본 형태는 여기서 다루지 않습니다. 대신 설계 단계에서 실제로 결정해야 하는 것들을 순서대로 정리해보겠습니다.

테이블을 나누는 이유

고객 이름과 주문 내역을 한 테이블에 같이 담으면 세 가지 문제가 생깁니다. 이상 현상(Anomaly)이라고 부르고, 정규화는 이 셋을 막기 위한 규칙입니다.

삽입 이상
아직 주문한 적 없는 고객을 등록하려는데 주문 정보 칸을 비워 둘 수 없어 넣지 못합니다. 넣고 싶은 것만 넣을 수 없는 상태입니다.
삭제 이상
마지막 주문 한 건을 취소했더니 그 고객의 이름과 연락처까지 함께 사라집니다. 지우고 싶지 않은 것까지 지워지는 상태입니다.
갱신 이상
고객이 이메일을 바꿨는데 주문 50건 중 30건만 고쳐졌습니다. 어느 값이 맞는지 알 수 없는 상태가 되어 가장 위험합니다.

세 가지 이상 현상의 원인은 같습니다. 성격이 다른 정보가 한 테이블에 섞여 있기 때문입니다. 고객은 customers에, 주문은 orders에 나눠 담고 주문 쪽에서 고객을 가리키게 하면 셋 다 사라집니다.

외래키를 걸 때 정하는 것

외래키를 선언하면 데이터베이스가 참조 무결성을 지켜 줍니다. 없는 고객 번호를 주문에 넣으려 하면 거부되고, 주문이 남아 있는 고객을 지우려 할 때도 막힙니다.

여기서 결정해야 하는 것이 부모를 지울 때 자식을 어떻게 할지입니다. 기본값에 맡기지 않고 업무 규칙에 맞게 골라야 합니다.

옵션 동작 쓰는 상황
ON DELETE CASCADE 부모를 지우면 자식도 함께 삭제 주문을 지우면 주문 항목도 같이 사라져야 할 때. 주문 없이 존재할 이유가 없는 데이터
ON DELETE RESTRICT 자식이 있으면 부모 삭제를 차단 카테고리를 지우기 전에 그 안의 상품을 먼저 정리하게 만들 때
ON DELETE SET NULL 부모를 지우면 자식의 외래키를 NULL로 담당 직원이 퇴사해도 고객 정보는 남기고 담당자만 비울 때
ON DELETE NO ACTION RESTRICT와 거의 같고 검사 시점이 다름 Oracle과 SQL Server의 기본값
ON UPDATE CASCADE 부모 키가 바뀌면 자식도 따라 변경 학번 같은 자연키가 바뀔 때. 애초에 대체키를 쓰는 편이 낫습니다 
CREATE TABLE order_items (
  order_id   BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity   INT    NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id)   REFERENCES orders(order_id)     ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT
);

같은 테이블 안에서도 옵션이 갈립니다. 주문이 사라지면 그 주문 항목은 남을 이유가 없으니 CASCADE이고, 상품은 주문 기록이 남아 있는 한 함부로 지우면 안 되니 RESTRICT입니다.

CASCADE는 연쇄됩니다. 부모의 부모까지 이어져 있으면 한 번의 DELETE로 예상보다 많은 행이 사라집니다. 삭제 대신 deleted_at 컬럼을 두고 지운 것으로 표시만 하는 방식(소프트 삭제)을 쓰는 경우가 많은 이유가 여기 있습니다.

관계와 교차 테이블

관계는 한쪽 행이 반대쪽 행 몇 개와 연결되는지로 세 가지로 나뉩니다. 이 중 실제로 설계 판단이 필요한 것은 N:M입니다.

N:M 관계는 외래키만으로 표현할 수 없어서 가운데 테이블이 반드시 들어갑니다. 주문 하나에 상품이 여러 개 담기고 같은 상품이 여러 주문에 들어가는 구조가 그렇습니다. order_items 같은 교차 테이블을 두면 다대다가 1:N 두 개로 풀립니다.

orders
PK order_id
1 → N
order_items
PK (order_id, product_id)
quantity · unit_price
N ← 1
products
PK product_id
가운데 교차 테이블에는 그 연결에만 붙는 정보를 함께 담습니다. 수량과 구매 시점 단가가 여기 들어가는데, 상품 가격은 나중에 바뀌어도 지난 주문의 결제 금액은 바뀌면 안 되기 때문입니다.

교차 테이블의 기본키는 두 외래키를 묶은 복합키로 두거나, 별도의 일련번호를 두는 방식 중에 고릅니다. 복합키로 두면 같은 조합이 두 번 들어가는 것을 데이터베이스가 막아 줍니다.

조회 방향이 양쪽 다 있다면 인덱스도 양방향으로 만들어 둡니다. 수강 테이블이라면 INDEX(student_id, course_id)와 INDEX(course_id, student_id)를 함께 두는 식입니다. 학생으로도 찾고 과목으로도 찾기 때문입니다.

약한 엔티티

약한 엔티티는 자기만의 기본키가 없어서 부모 없이는 구분되지 않는 테이블입니다. 주문 항목은 몇 번째 항목인지만으로는 식별되지 않고, 어느 주문의 몇 번째인지가 있어야 구분됩니다.

  • 부모의 기본키와 자신의 부분키를 묶어 복합 기본키를 만듭니다.
  • 부모가 사라지면 존재할 이유가 없으므로 ON DELETE CASCADE를 겁니다.

정규화를 어디까지

정규화 단계는 여러 단계로 나뉘지만, 내용은 전부 한 테이블에 성격이 다른 정보를 섞지 말라는 말입니다.

정규형 조건 없애는 것 예
1NF 한 칸에 값 하나만 반복 그룹, 다중값 속성 과목 = 'DB, 네트워크' 를 두 행으로 분리
2NF 1NF + 부분 함수 종속 제거 복합키의 일부에만 딸린 속성 (학번, 과목코드)가 키인데 학생 이름은 학번에만 딸림
3NF 2NF + 이행적 종속 제거 키가 아닌 속성끼리의 종속 학번 → 학과코드 → 학과명 사슬을 끊음
BCNF 3NF + 모든 결정자가 후보키 후보키가 아닌 결정자 3NF를 만족해도 남는 예외를 정리

실무에서는 보통 3NF 또는 BCNF까지 적용합니다. 더 나눌수록 중복은 줄지만 조회할 때 이어 붙일 테이블이 늘어나 JOIN 비용이 커지기 때문입니다.

그래서 조회가 아주 잦은 곳에서는 일부러 중복을 남기는 반정규화를 검토합니다. 다만 순서가 있습니다. 정규화가 기본이고, 반정규화는 측정한 근거를 갖고 되돌리는 선택입니다. 처음부터 반정규화된 구조로 시작하면 갱신 이상을 애플리케이션이 전부 떠안게 됩니다.

개념 · 논리 · 물리 세 단계

모델링은 한 번에 끝나지 않고 추상적인 것에서 구체적인 것으로 세 단계를 거칩니다. 각 단계에서 정하는 것이 다르고, 앞 단계를 건너뛰면 뒤에서 되돌아오게 됩니다.

① 개념적
요구사항에서 명사를 뽑아 엔티티를 찾습니다.
관계와 카디널리티를 정하고 ERD를 그립니다.
DBMS와 무관합니다.
→
② 논리적
엔티티를 테이블로 옮깁니다.
PK와 FK를 정하고 정규화를 적용합니다.
아직 DBMS와 무관합니다.
→
③ 물리적
쓰는 DBMS에 맞춥니다.
데이터 타입과 인덱스를 정합니다.
여기서 제품별 차이가 나옵니다.

타입을 정할 때 걸리는 것

물리 설계에서 자주 실수하는 세 가지가 있습니다. 전부 나중에 바꾸기 어려운 항목입니다.

  • 금액은 NUMERIC(10,2)로 둡니다. FLOAT이나 DOUBLE은 이진 부동소수점이라 0.1 + 0.2가 정확히 0.3이 되지 않습니다. 합계가 1원씩 어긋나는 정산 문제가 여기서 나옵니다.
  • 시각은 TIMESTAMPTZ로 둡니다. 타임존 없이 저장하면 서버 지역이 바뀌거나 사용자가 해외에 있을 때 시각이 어긋납니다.
  • 문자열은 최대 길이를 정해 둡니다. PostgreSQL에서 TEXT와 VARCHAR(n)은 저장 방식이 같지만, 인덱스를 걸 컬럼이라면 길이를 막아 두는 편이 낫습니다. 긴 값이 그대로 인덱스 키가 되면 트리가 깊어집니다.

정리하면

설계할 때 명시적으로 정하고 넘어가야 하는 것들입니다. 기본값에 맡기면 나중에 문제가 됩니다.

  • 외래키마다 삭제 옵션을 정합니다. 함께 지울지(CASCADE), 막을지(RESTRICT), 비울지(SET NULL).
  • N:M은 교차 테이블로 풀고, 그 연결에만 붙는 값을 함께 담습니다. 구매 시점 단가가 대표적입니다.
  • 정규화는 3NF까지 가고, 반정규화는 측정한 뒤에 합니다. 순서를 바꾸면 갱신 이상을 코드가 떠안습니다.
  • 금액은 NUMERIC, 시각은 TIMESTAMPTZ. 데이터가 쌓인 뒤에는 바꾸기 어렵습니다.
  • 카디널리티는 업무 규칙으로 정합니다. 지금 하나인 것과 앞으로도 하나여야 하는 것은 다릅니다.
한 줄 정리: 모델링에서 어려운 것은 문법이 아니라 되돌리기 어려운 결정들입니다. 삭제 옵션, 정규화 수준, 컬럼 타입, 카디널리티 네 가지는 설계할 때 근거를 갖고 정해 둡니다.

참고