테이블 설계는 값을 담을 그릇을 정하는 일이다
SQL을 잘 작성해도 테이블 구조가 흔들리면 데이터는 오래 버티지 못한다. 예를 들어 상품 가격을 text로 저장하면 화면에는 보일 수 있다. 하지만 "1만 원 이상 상품"을 찾거나 합계를 낼 때마다 형 변환이 필요하다. 전화번호를 integer로 저장하면 앞의 0이 사라지고, 010-1234-5678 같은 형식도 담을 수 없다.
테이블 설계는 먼저 업무 문장을 열로 바꾸는 작업이다.
고객은 이메일, 이름, 전화번호, 우편번호를 가진다.
상품은 상품 코드, 이름, 가격, 재고 수량을 가진다.
주문은 주문 번호, 고객, 주문 상태, 주문 시각, 결제 시각, 총금액, 배송 주소를 가진다.
그다음 각 값에 맞는 타입과 제약 조건을 붙인다. 타입은 "어떤 종류의 값인가"를 제한하고, 제약 조건은 "어떤 값이어야 유효한가"를 제한한다. PostgreSQL은 정수, 정확한 수치, 문자열, 날짜와 시각, 불리언, UUID 같은 기본 타입을 제공한다1.
AI 생성 이미지
자주 쓰는 타입을 고르는 기준
정수 값은 보통 integer로 시작한다. 수량, 재고, 조회 수처럼 소수점이 없는 값에 적합하다. 값이 매우 커질 수 있는 식별자에는 bigint를 쓴다. 주문이나 로그처럼 행이 오래 쌓이는 테이블의 자동 증가 ID는 bigint GENERATED ALWAYS AS IDENTITY를 기본 선택지로 두면 안전하다. smallint는 범위가 작기 때문에 상태 코드처럼 값의 범위가 확실할 때만 쓴다.
금액은 numeric(p, s)를 사용한다. p는 전체 자릿수, s는 소수 자릿수다. 예를 들어 numeric(12, 2)는 소수점 아래 2자리를 포함해 전체 12자리까지 저장한다. 상품 가격, 주문 총액, 정산 금액처럼 정확해야 하는 값에는 real이나 double precision보다 numeric이 적합하다. 부동소수점 타입은 근사값을 다루기 때문에 금융 데이터의 합계에서 예상과 다른 결과를 만들 수 있다.
문자열은 대부분 text로 충분하다. PostgreSQL에서는 길이를 모르는 이름, 설명, 주소에 text를 자연스럽게 사용한다. varchar(n)은 "업무상 반드시 n자를 넘으면 안 된다"는 규칙이 있을 때 선택한다. 예를 들어 상품 코드가 최대 40자라는 규칙이 확정되어 있으면 varchar(40)도 가능하다. 반대로 고객 이름이나 주소처럼 길이가 바뀔 수 있는 값에 작은 제한을 걸면 나중에 정상 데이터를 막을 수 있다.
시각은 timestamp와 timestamptz를 구분해야 한다. timestamp는 시간대 정보가 없는 날짜와 시각이다. "매장 현지 시간으로 매일 오전 9시에 시작" 같은 지역 일정에 어울린다. timestamptz는 시간대를 고려하는 시각이다. 주문 시각, 결제 시각, 로그인 시각처럼 전 세계 어디서 보더라도 같은 순간을 가리켜야 하는 값은 timestamptz로 저장한다. 다만 timestamptz가 사용자가 입력한 원래 시간대 문자열까지 보존하는 것은 아니다. 같은 순간을 세션의 시간대 설정에 맞춰 보여준다고 이해하면 된다.
boolean은 참과 거짓만 필요한 값에 쓴다. is_active, is_deleted, is_public처럼 질문이 예/아니오로 끝나면 적합하다. 상태가 셋 이상이면 boolean이 아니라 status text와 CHECK 제약 조건을 쓰는 편이 낫다. 주문 상태는 created, paid, cancelled, shipped처럼 여러 값이 필요하기 때문이다.
uuid는 전역적으로 충돌 가능성이 매우 낮은 식별자다. 여러 시스템에서 동시에 ID를 만들거나, 외부 API에 노출되는 식별자를 순차 숫자로 보여주고 싶지 않을 때 유용하다. 내부에서만 쓰는 대량 쓰기 테이블은 bigint identity가 단순하고, 외부에 공개되는 고객 ID나 추적 ID는 uuid가 편하다.
고객, 상품, 주문 테이블 만들기
다음 예제는 쇼핑몰의 기본 테이블을 만든다. 실습 전에 같은 이름의 테이블이 있다면 삭제한다. orders가 customers를 참조하므로 삭제할 때는 주문 테이블을 먼저 지운다.
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;고객 테이블부터 만든다.
CREATE TABLE customers (
customer_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
email text NOT NULL,
name text NOT NULL,
phone_number text,
postal_code text,
is_active boolean NOT NULL DEFAULT true,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT customers_email_unique UNIQUE (email),
CONSTRAINT customers_email_not_blank CHECK (length(trim(email)) > 0),
CONSTRAINT customers_name_not_blank CHECK (length(trim(name)) > 0)
);customer_id는 고객 한 명을 식별하는 기본 키다. 여기서는 uuid 값을 자동으로 만들기 위해 gen_random_uuid()를 기본값으로 사용했다. 이 책에서 권장한 PostgreSQL 16, 17, 18 환경에서는 바로 사용할 수 있는 UUID 생성 함수다. 이메일은 로그인이나 알림에 쓰일 수 있으므로 NOT NULL과 UNIQUE를 함께 둔다. 전화번호와 우편번호는 숫자가 아니라 문자열이다. 계산 대상이 아니고, 앞자리 0이나 +82, 하이픈 같은 문자가 의미를 가질 수 있기 때문이다.
상품 테이블은 다음과 같이 만든다.
CREATE TABLE products (
product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku text NOT NULL,
name text NOT NULL,
price numeric(12, 2) NOT NULL,
stock_quantity integer NOT NULL DEFAULT 0,
is_active boolean NOT NULL DEFAULT true,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT products_sku_unique UNIQUE (sku),
CONSTRAINT products_sku_not_blank CHECK (length(trim(sku)) > 0),
CONSTRAINT products_name_not_blank CHECK (length(trim(name)) > 0),
CONSTRAINT products_price_non_negative CHECK (price >= 0),
CONSTRAINT products_stock_non_negative CHECK (stock_quantity >= 0)
);product_id는 내부 식별자이므로 자동 증가하는 bigint를 사용했다. sku는 재고 관리나 외부 연동에서 쓰는 상품 코드다. 같은 상품 코드가 두 번 들어가면 안 되므로 UNIQUE를 둔다. 가격과 재고 수량은 음수가 되면 업무적으로 이상하므로 CHECK로 막는다.
주문 테이블은 주문의 머리 정보만 담는다. 실제 쇼핑몰에서 한 주문에 여러 상품을 담으려면 주문 상세 테이블이 따로 필요하다. 여기서는 타입과 제약 조건에 집중하기 위해 총금액과 상태를 중심으로 설계한다.
CREATE TABLE orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_no text NOT NULL,
customer_id uuid NOT NULL REFERENCES customers(customer_id),
status text NOT NULL DEFAULT 'created',
ordered_at timestamptz NOT NULL DEFAULT now(),
paid_at timestamptz,
total_amount numeric(12, 2) NOT NULL,
shipping_postal_code text NOT NULL,
shipping_address text NOT NULL,
CONSTRAINT orders_order_no_unique UNIQUE (order_no),
CONSTRAINT orders_order_no_not_blank CHECK (length(trim(order_no)) > 0),
CONSTRAINT orders_total_amount_non_negative CHECK (total_amount >= 0),
CONSTRAINT orders_status_check CHECK (status IN ('created', 'paid', 'cancelled', 'shipped')),
CONSTRAINT orders_paid_at_check CHECK (paid_at IS NULL OR paid_at >= ordered_at)
);customer_id는 customers 테이블의 customer_id를 참조한다. 이렇게 외래 키를 두면 존재하지 않는 고객으로 주문을 만들 수 없다. ordered_at과 paid_at은 실제 사건이 발생한 시각이다. 사용자가 한국에 있든 다른 시간대에 있든 같은 순간을 표현해야 하므로 timestamptz를 사용한다. paid_at은 아직 결제 전이면 값이 없을 수 있으므로 NOT NULL을 붙이지 않았다.
테이블을 만든 뒤에는 psql에서 구조를 확인한다.
d customers
d products
d orders테이블 구조를 확인하는 습관은 중요하다. SQL 파일을 실행했다고 끝내지 말고, 실제로 타입과 제약 조건이 의도대로 들어갔는지 확인해야 한다.
제약 조건은 잘못된 데이터를 입구에서 막는다
제약 조건은 애플리케이션 코드보다 데이터베이스에 가까운 방어선이다. PostgreSQL은 제약 조건을 위반하는 값을 저장하려고 하면 오류를 발생시킨다2.
기본 키는 한 행을 고유하게 식별한다. PRIMARY KEY는 중복을 허용하지 않고 NULL도 허용하지 않는다. 고객의 customer_id, 상품의 product_id, 주문의 order_id가 여기에 해당한다.
NOT NULL은 반드시 값이 있어야 하는 열에 붙인다. 고객 이름, 상품 가격, 주문 총금액처럼 값이 없으면 업무 처리가 불가능한 열에 필요하다.
UNIQUE는 중복되면 안 되는 값에 붙인다. 이메일, 상품 코드, 주문 번호처럼 한 번만 등장해야 하는 값이 대상이다. 단, nullable 열에 UNIQUE만 붙이면 NULL 처리 방식 때문에 기대와 다를 수 있다. "반드시 하나의 고유한 값"이어야 한다면 NOT NULL과 UNIQUE를 함께 사용한다.
CHECK는 값의 범위나 목록을 제한한다. 가격은 0 이상, 재고도 0 이상, 주문 상태는 정해진 값 중 하나여야 한다.
외래 키는 다른 테이블에 실제로 존재하는 값을 참조하게 만든다. 주문의 customer_id처럼 "이 주문은 반드시 존재하는 고객의 주문이어야 한다"는 규칙을 데이터베이스에 맡기는 것이다.
다음 SQL은 실패해야 정상이다.
INSERT INTO products (sku, name, price, stock_quantity)
VALUES ('BAD-001', '잘못된 상품', -1000, 10);가격이 음수이므로 products_price_non_negative 제약 조건을 위반한다.
이메일 중복도 막힌다.
INSERT INTO customers (email, name)
VALUES ('[email protected]', '김민수');
INSERT INTO customers (email, name)
VALUES ('[email protected]', '김민수2');두 번째 입력은 customers_email_unique 제약 조건 때문에 실패한다.
주문 상태도 정해진 값만 허용된다.
WITH new_customer AS (
INSERT INTO customers (email, name)
VALUES ('[email protected]', '이서연')
RETURNING customer_id
)
INSERT INTO orders (
order_no,
customer_id,
status,
total_amount,
shipping_postal_code,
shipping_address
)
SELECT
'ORD-20260530-001',
customer_id,
'waiting',
39000,
'06236',
'서울시 강남구 테헤란로 1'
FROM new_customer;waiting은 허용된 주문 상태가 아니므로 실패한다. 이런 오류는 불편한 것이 아니라 필요한 신호다. 데이터베이스가 잘못된 데이터를 거절하고 있다는 뜻이다.
자주 실수하는 타입 선택
전화번호와 우편번호는 integer가 아니다. 숫자처럼 보여도 계산하지 않는다. 앞자리 0, 국가번호, 하이픈, 공백이 의미를 가질 수 있으므로 text로 저장한다.
금액은 numeric으로 저장한다. 정수 원화만 다룬다면 numeric(12, 0)도 가능하지만, 할인율, 세금, 해외 통화, 정산 데이터를 생각하면 numeric(12, 2)처럼 소수 자릿수를 명시하는 편이 확장에 유리하다.
생성 시각과 수정 시각은 보통 timestamptz가 맞다. 서버가 한국에 있어도 나중에 해외 사용자가 생기거나, 클라우드 서버의 시간대가 UTC로 설정될 수 있다. 사건이 발생한 "순간"을 저장해야 한다면 시간대를 고려해야 한다.
상태값은 boolean으로 무리하게 줄이지 않는다. 주문이 결제됨/안 됨만 있으면 is_paid boolean이 가능하다. 하지만 생성, 결제, 취소, 배송처럼 흐름이 있다면 status 열을 만들고 허용 값을 제한한다.
문자열 길이는 신중하게 제한한다. varchar(10)처럼 작은 제한은 당장은 깔끔해 보여도 실제 이름, 주소, 상품명에서 금방 문제가 된다. 길이 제한이 업무 규칙이 아니라면 text를 쓰고, 꼭 필요한 규칙만 CHECK나 varchar(n)으로 표현한다.
설계할 때 마지막으로 확인할 것
테이블을 만들기 전에 다음 질문을 확인한다.
이 값은 계산하는 값인가, 표시만 하는 값인가?
값이 없을 수 있는가, 반드시 있어야 하는가?
중복되어도 되는가?
음수나 빈 문자열이 들어와도 되는가?
이 시각은 지역 일정인가, 실제 사건이 발생한 순간인가?
외부에 노출되는 ID인가, 내부 처리용 ID인가?
좋은 테이블 구조는 많은 오류를 미리 막는다. 애플리케이션에서 한 번 더 검증하더라도, 데이터베이스의 타입과 제약 조건이 마지막 방어선이 되어야 한다. 이 기준을 지키면 쿼리는 단순해지고, 데이터는 시간이 지나도 해석 가능한 상태로 남는다.
참고
이 노트는 보도·비평·교육·연구 목적으로 작성되었습니다. 저작권 문의가 있으시면 에서 알려주세요.