스키마 설계와 정규화 감각 기르기

스키마 설계는 테이블 이름을 정하는 일이 아니다

스키마 설계는 업무 규칙을 데이터 구조로 바꾸는 일이다. 여기서 스키마는 두 가지 의미로 쓰인다. 하나는 PostgreSQL의 public, sales 같은 이름 공간이고, 다른 하나는 테이블, 열, 제약 조건, 관계를 포함한 전체 데이터 구조다. 이 장에서 다루는 핵심은 두 번째 의미다.

좋은 설계는 쿼리를 어렵게 만들지 않는다. 오히려 반대다. 주문에 필요한 데이터가 고객, 주문, 주문 상세, 결제, 배송으로 적절히 나뉘어 있으면 조인과 집계가 자연스러워진다. 반대로 한 테이블에 모든 값을 몰아넣으면 처음에는 편해 보이지만, 수정과 검증이 어려워진다.

예를 들어 쇼핑몰 요구사항이 다음과 같다고 하자.

• 고객은 여러 번 주문할 수 있다.

• 한 주문에는 여러 상품이 들어갈 수 있다.

• 주문은 결제와 배송 정보를 가진다.

• 상품 가격은 바뀔 수 있지만, 주문 당시의 판매가는 보존해야 한다.

• 결제 실패 이력도 남겨야 한다.

이 문장에서 먼저 찾아야 할 것은 엔터티, 속성, 관계다.

• 엔터티: 고객, 주문, 주문 상세, 상품, 결제, 배송

• 속성: 고객 이메일, 주문 시각, 주문 상태, 상품명, 수량, 결제 금액, 배송 상태

• 관계: 고객 1명은 주문 N건, 주문 1건은 주문 상세 N건, 주문 1건은 결제 N건 가능

이 과정을 거치면 다음과 같은 기본 구조가 나온다.

CREATE TABLE customers (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE,
    name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    current_price numeric(12, 2) NOT NULL CHECK (current_price >= 0),
    is_active boolean NOT NULL DEFAULT true
);

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(id),
    status text NOT NULL CHECK (status IN ('draft', 'paid', 'shipping', 'completed', 'cancelled')),
    ordered_at timestamptz NOT NULL DEFAULT now(),
    total_amount numeric(12, 2) NOT NULL DEFAULT 0 CHECK (total_amount >= 0)
);

CREATE TABLE order_items (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES orders(id),
    product_id bigint NOT NULL REFERENCES products(id),
    product_name_at_order text NOT NULL,
    unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
    quantity integer NOT NULL CHECK (quantity > 0),
    UNIQUE (order_id, product_id)
);

여기서 product_name_at_order, unit_price는 단순 중복처럼 보일 수 있다. 하지만 주문 당시의 상품명과 판매가를 보존해야 한다면 이것은 필요한 기록이다. 현재 상품명이 바뀌어도 과거 영수증의 상품명까지 바뀌면 안 된다. 설계에서는 "중복처럼 보이는 값"과 "업무상 보존해야 하는 시점의 값"을 구분해야 한다.

AI 생성 이미지

제1정규형: 한 칸에는 하나의 값만 둔다

제1정규형은 각 열의 값이 더 쪼개지지 않는 하나의 값이어야 한다는 원칙이다. PostgreSQL은 배열과 JSON을 지원하지만, 관계를 표현해야 하는 데이터를 배열에 넣으면 조회와 검증이 어려워진다.

나쁜 예를 보자.

CREATE TABLE bad_orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    product_ids bigint[] NOT NULL,
    quantities integer[] NOT NULL,
    ordered_at timestamptz NOT NULL DEFAULT now()
);

이 구조는 한 주문에 여러 상품을 담을 수 있다는 점에서는 편해 보인다. 하지만 다음 질문에 답하기 어렵다.

• 특정 상품이 포함된 주문은 몇 건인가?

• 상품별 판매 수량은 얼마인가?

• product_ids와 quantities의 배열 길이는 항상 같은가?

• 존재하지 않는 상품 ID가 배열 안에 들어가는 것을 어떻게 막을 것인가?

관계형 구조에서는 반복되는 값을 별도 테이블로 분리한다.

CREATE TABLE order_items (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES orders(id),
    product_id bigint NOT NULL REFERENCES products(id),
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0)
);

이제 상품별 판매량은 자연스럽게 집계할 수 있다.

SELECT
    product_id,
    sum(quantity) AS sold_quantity,
    sum(quantity * unit_price) AS sales_amount
FROM order_items
GROUP BY product_id;

배열과 JSON은 쓸모가 없다는 뜻이 아니다. 검색 조건, 조인, 집계, 제약 조건의 대상이 되는 값이라면 먼저 열과 테이블로 표현하는 것이 안전하다. JSON과 배열은 구조가 자주 바뀌거나 부가 정보에 가까운 데이터에 더 어울린다.

제2정규형: 복합 키 전체에 의존하게 한다

제2정규형은 기본 키의 일부에만 의존하는 열을 분리하라는 원칙이다. 특히 복합 기본 키를 사용할 때 중요하다.

예를 들어 주문 상세의 기본 키를 (order_id, product_id)로 잡았다고 하자.

CREATE TABLE bad_order_items (
    order_id bigint NOT NULL,
    product_id bigint NOT NULL,
    product_name text NOT NULL,
    customer_email text NOT NULL,
    quantity integer NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

이 테이블에는 의존 관계가 섞여 있다.

• quantity는 order_id와 product_id 조합에 의존한다.

• product_name은 보통 product_id에 의존한다.

• customer_email은 order_id를 통해 고객에 의존한다.

이런 구조에서는 같은 상품명이 여러 주문 상세에 반복 저장된다. 상품명을 수정해야 하면 많은 행을 바꿔야 하고, 일부 행만 바뀌면 데이터가 서로 달라진다.

정리하면 다음과 같다.

CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(id)
);

CREATE TABLE order_items (
    order_id bigint NOT NULL REFERENCES orders(id),
    product_id bigint NOT NULL REFERENCES products(id),
    quantity integer NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id)
);

단, 앞에서 본 것처럼 주문 당시 상품명이나 가격을 보존해야 한다면 order_items에 별도 스냅샷 열을 둘 수 있다. 이때는 "현재 상품 정보"가 아니라 "주문 당시 기록"이라는 의미가 열 이름에 드러나야 한다. product_name보다 product_name_at_order가 낫다.

제3정규형: 다른 일반 열에 의존하는 값을 분리한다

제3정규형은 기본 키가 아닌 열에 의존하는 열을 분리하라는 원칙이다. 쉽게 말해 "A를 알면 B가 정해지는 값"이 같은 테이블에 반복된다면 분리 후보로 본다.

배송 데이터를 예로 들어 보자.

CREATE TABLE bad_shipments (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL,
    shipping_method_code text NOT NULL,
    shipping_method_name text NOT NULL,
    base_fee numeric(12, 2) NOT NULL,
    status text NOT NULL
);

shipping_method_name과 base_fee가 shipping_method_code에 의해 정해진다면, 배송 행마다 반복 저장할 이유가 약하다. 배송 방법 테이블을 따로 두는 편이 낫다.

CREATE TABLE shipping_methods (
    code text PRIMARY KEY,
    name text NOT NULL,
    base_fee numeric(12, 2) NOT NULL CHECK (base_fee >= 0),
    is_active boolean NOT NULL DEFAULT true
);

CREATE TABLE shipments (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES orders(id),
    shipping_method_code text NOT NULL REFERENCES shipping_methods(code),
    status text NOT NULL CHECK (status IN ('ready', 'shipped', 'delivered', 'returned')),
    shipped_at timestamptz,
    delivered_at timestamptz
);

이제 배송 방법 이름이 바뀌면 shipping_methods 한 행만 수정하면 된다. 또한 존재하지 않는 배송 방법 코드가 배송 테이블에 들어가는 것도 외래 키로 막을 수 있다.

정규화의 목적은 테이블 수를 늘리는 것이 아니다. 수정해야 할 곳을 한 곳으로 만들고, 잘못된 조합이 저장되지 않게 하는 것이다.

외래 키와 ON DELETE 옵션은 업무 규칙이다

외래 키는 두 테이블 사이의 약속이다. orders.customer_id가 customers.id를 참조한다면, 주문은 반드시 존재하는 고객에 속해야 한다.

CREATE TABLE payments (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES orders(id),
    payment_no text NOT NULL UNIQUE,
    amount numeric(12, 2) NOT NULL CHECK (amount > 0),
    status text NOT NULL CHECK (status IN ('ready', 'paid', 'failed', 'cancelled')),
    paid_at timestamptz
);

기본 외래 키는 참조 대상 행이 삭제될 때 삭제를 막는다. 고객에게 주문이 있으면 고객 행을 지울 수 없는 식이다. 이것은 불편함이 아니라 데이터 보호 장치다.

삭제 동작은 ON DELETE로 정할 수 있다.

CREATE TABLE order_items (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id bigint NOT NULL REFERENCES products(id),
    quantity integer NOT NULL CHECK (quantity > 0)
);

주요 선택지는 다음과 같다.

• NO ACTION: 기본값이다. 참조하는 자식 행이 있으면 부모 삭제를 허용하지 않는다.

• RESTRICT: 삭제를 제한한다. 일반적인 사용감은 NO ACTION과 비슷하지만, 지연 검사가 필요한 고급 상황에서는 차이가 있다.

• CASCADE: 부모가 삭제되면 자식도 함께 삭제된다.

• SET NULL: 부모가 삭제되면 자식의 외래 키 값을 NULL로 바꾼다.

• SET DEFAULT: 부모가 삭제되면 자식의 외래 키 값을 기본값으로 바꾼다.

실무에서는 CASCADE를 조심해야 한다. 주문을 지웠을 때 주문 상세가 같이 지워지는 것은 자연스러울 수 있다. 하지만 결제, 배송, 정산 이력까지 연쇄 삭제되면 법적·회계적 문제가 생길 수 있다. 운영 데이터에서는 물리 삭제보다 상태 변경을 쓰는 경우가 많다.

UPDATE orders
SET status = 'cancelled'
WHERE id = 1001
  AND status IN ('draft', 'paid')
RETURNING id, status;

외래 키를 만들 때 한 가지 더 기억할 점이 있다. PostgreSQL은 참조 대상의 기본 키나 유니크 키에는 인덱스가 있지만, 외래 키가 있는 자식 열에는 자동으로 인덱스를 만들지 않는다. 조인이나 부모 삭제 검사가 자주 일어나는 열이라면 직접 인덱스를 만드는 것이 좋다.

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_payments_order_id ON payments(order_id);

인덱스 설계는 뒤 장에서 더 자세히 다루겠지만, 외래 키 열은 조인 조건으로 자주 쓰인다는 점을 기억해 두자.

반정규화는 성능 개선이 아니라 책임 추가다

정규화를 잘했다고 해서 항상 모든 조회가 빠른 것은 아니다. 주문 목록 화면에서 주문 금액을 매번 주문 상세에서 계산하면 비용이 클 수 있다. 그래서 orders.total_amount처럼 합계 값을 저장하기도 한다.

이것이 반정규화다. 이미 계산할 수 있는 값을 저장해 조회를 단순하게 만드는 방식이다.

SELECT
    o.id,
    o.ordered_at,
    o.status,
    o.total_amount
FROM orders o
WHERE o.customer_id = 10
ORDER BY o.ordered_at DESC
LIMIT 20;

문제는 orders.total_amount와 order_items의 실제 합계가 달라질 수 있다는 점이다. 반정규화를 선택했다면 반드시 검증 쿼리를 함께 가져야 한다.

SELECT
    o.id,
    o.total_amount,
    coalesce(sum(oi.quantity * oi.unit_price), 0) AS calculated_amount
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.total_amount
HAVING o.total_amount <> coalesce(sum(oi.quantity * oi.unit_price), 0);

이 쿼리 결과가 나오면 저장된 합계와 상세 합계가 맞지 않는 주문이 있다는 뜻이다. 운영에서는 이런 검증 쿼리를 배치 작업, 모니터링, 정산 전 점검 절차에 넣어야 한다.

반정규화를 고려할 때는 다음 질문에 답해야 한다.

1. 원본 데이터의 기준 테이블은 어디인가?

2. 중복 저장된 값은 언제, 누가, 어떤 트랜잭션에서 갱신하는가?

3. 값이 어긋났는지 확인하는 쿼리가 있는가?

4. 어긋난 값을 복구하는 절차가 있는가?

5. 이 중복이 단순 편의인지, 성능 측정 결과 필요한 선택인지 확인했는가?

자주 쓰는 반정규화 방식에는 합계 저장, 최근 상태 저장, 검색용 텍스트 열, 통계 테이블, 물리화 뷰가 있다. 모두 나쁜 것은 아니다. 다만 반정규화는 쿼리 하나를 줄이는 대신 데이터 일관성을 관리할 책임을 만든다.

설계를 검증하는 간단한 기준

테이블을 나눌지 말지 고민될 때는 다음 기준으로 판단해 보자.

• 같은 값이 여러 행에 반복되고, 수정될 가능성이 있는가?

• 한 열에 여러 값이 들어가고 있는가?

• 어떤 열이 기본 키가 아니라 다른 일반 열에 의해 결정되는가?

• 삭제했을 때 함께 사라져도 되는 데이터와 보존해야 하는 데이터가 섞여 있는가?

• 화면 요구 때문에 저장한 값이라면 원본과 대조할 방법이 있는가?

정규화는 외우는 규칙이 아니라 데이터가 변할 때 깨지지 않게 만드는 감각이다. 처음부터 완벽한 ERD를 그리기는 어렵다. 대신 요구사항 문장에서 엔터티, 속성, 관계를 찾아내고, 외래 키와 제약 조건으로 업무 규칙을 데이터베이스에 새기는 습관을 들이면 된다.

PostgreSQL은 잘못된 참조, 잘못된 상태값, 음수 금액, 중복 이메일 같은 문제를 제약 조건으로 막을 수 있다. 좋은 스키마 설계는 애플리케이션 코드보다 아래에서 데이터를 지키는 마지막 방어선이다.