SQL 11회차 : CREATE/ALTER와 타입 설계 (문자/숫자/날짜, 기본값, “배송 상태 관리” 테이블 실습)

테이블 설계에서 가장 자주 실수하는 지점은 “컬럼 타입을 아무거나 고르는 것”과 “기본값/NULL 정책을 뒤늦게 수습하는 것”입니다. 이번 회차에서는 CREATE/ALTER로 스키마를 만들고 바꾸는 기본 흐름을 익힌 뒤, 문자/숫자/날짜 타입을 어떻게 선택하면 좋은지 초보자 관점에서 정리합니다. 마지막에는 실습으로 “배송 상태 관리” 테이블을 설계해보며, 상태 코드/기본값/인덱스까지 한 번에 연결해봅니다.

목차

• 핵심 포인트
• CREATE/ALTER 기본 감각
• 데이터 타입 빠른 지도
• 문자 타입 설계(CHAR/VARCHAR/TEXT)
• 숫자 타입 설계(INT/DECIMAL 등)
• 날짜/시간 타입 설계(DATE/DATETIME/TIMESTAMP)
• 기본값(DEFAULT)과 NULL 정책
• 실습: “배송 상태 관리” 테이블 설계
• 실행 단계 체크리스트
• 추가로 생각해볼 점
• 블로그 최적화 정보

핵심 포인트

• CREATE TABLE로 “처음부터 깔끔하게” 만드는 것이 가장 싸고, ALTER TABLE은 “필요할 때 최소 변경”이 기본 전략입니다.
• 문자 타입은 “길이/변동 여부/검색 패턴”을 기준으로 고릅니다(예: 코드=짧고 고정, 메모=길고 자유).
• 숫자 타입은 “정확도(특히 돈)”가 핵심입니다. 금액/요금은 보통 DECIMAL 계열이 안전합니다.
• 날짜/시간 타입은 “시간대(timezone)와 자동 갱신 정책”이 함께 고려되어야 합니다.
• 기본값(DEFAULT)과 NULL/NOT NULL 정책은 데이터 품질을 좌우합니다. “모르겠으면 NULL”이 아니라, “규칙이 있으면 NOT NULL + DEFAULT”가 편합니다.

CREATE/ALTER 기본 감각

CREATE는 “처음 만들기”, ALTER는 “이미 있는 것 바꾸기”입니다. 초보자에게 중요한 감각은 다음 3가지입니다.

• 스키마는 제품의 규칙집: 나중에 애플리케이션 로직으로 땜질하기보다, DB 제약과 타입으로 기본 품질을 확보하는 편이 안정적입니다.
• ALTER는 비용이 든다: 컬럼 타입 변경/제약 추가는 데이터가 많을수록 잠금/시간이 커질 수 있습니다. 운영에서는 특히 신중해야 합니다.
• 작게, 자주, 안전하게: 큰 ALTER 한 번보다, 영향이 작은 변경을 나눠서 하는 편이 장애 위험을 줄일 때가 많습니다.

CREATE/ALTER에서 자주 쓰는 작업

작업	명령 예	주의 포인트
테이블 생성	CREATE TABLE ...	PK, NOT NULL, DEFAULT를 초기에 정리
컬럼 추가	ALTER TABLE ... ADD COLUMN ...	기본값/NULL 정책이 기존 데이터에 미치는 영향
컬럼 타입 변경	ALTER TABLE ... ALTER/MODIFY ...	대규모 테이블에서 잠금/시간 비용 증가 가능
제약 추가	PRIMARY KEY, FOREIGN KEY, CHECK	기존 데이터가 규칙을 어기면 추가가 실패할 수 있음
인덱스 추가	CREATE INDEX ...	쓰기 성능/저장 공간과의 트레이드오프

데이터 타입 빠른 지도

타입 선택은 결국 “저장할 값의 성격”을 정확히 말로 정의하는 작업입니다. 아래 표는 초보자가 자주 만나는 타입을 한 번에 정리한 지도입니다.

분류	대표 타입	언제 쓰나	초보자 팁
문자	CHAR, VARCHAR, TEXT	코드/이름/주소/메모	짧고 고정이면 CHAR, 길이 변동이면 VARCHAR, 매우 길면 TEXT
정수	INT, BIGINT	ID/수량/카운트	ID는 성장 범위를 고려해 BIGINT도 흔함
소수	DECIMAL, NUMERIC	금액/요금/정확도가 필요한 수	돈은 FLOAT보다 DECIMAL을 우선 검토
날짜/시간	DATE, DATETIME, TIMESTAMP	생성일/수정일/이벤트 시각	타임존 정책을 팀 기준으로 정하고 일관되게 적용

문자 타입 설계(CHAR/VARCHAR/TEXT)

문자 타입은 “길이가 얼마나 안정적인가”와 “검색/정렬에 얼마나 쓰이는가”를 기준으로 결정하는 것이 무난합니다.

CHAR vs VARCHAR

• CHAR(n): 길이가 고정에 가까운 값에 적합합니다(예: 상태 코드, 국가 코드처럼 규격이 고정된 값).
• VARCHAR(n): 길이가 달라질 수 있는 값에 적합합니다(예: 이름, 택배사명, 트래킹 번호).

TEXT는 언제 쓰나

• 사용자 메모, 긴 설명, 이벤트 로그처럼 길이가 길고 예측이 어려운 값에 사용합니다.
• 다만 검색/정렬/인덱싱 방식이 DB별로 제한될 수 있으니, 핵심 검색 키는 별도 컬럼으로 분리하는 설계를 고려합니다.

숫자 타입 설계(INT/DECIMAL 등)

숫자는 “정수인지, 소수인지”가 첫 번째 갈림길입니다.

• 정수: 수량/카운트/ID처럼 소수점이 의미가 없으면 INT 계열로 단순하게 갑니다.
• 정확한 소수: 금액처럼 오차가 허용되면 안 되는 값은 DECIMAL/NUMERIC 계열을 검토합니다.

배송 상태 관리에서는 보통 큰 금액보다는 “수량/정렬순서/상태 단계 번호” 정도가 많기 때문에 정수 타입이 중심입니다. 예를 들어 상태 코드 테이블에 sort_order를 두면 화면 표시 순서를 안정적으로 관리할 수 있습니다.

날짜/시간 타입 설계(DATE/DATETIME/TIMESTAMP)

날짜/시간 타입은 다음 질문을 먼저 하고 시작하면 실수가 줄어듭니다.

• 이 값은 “날짜만” 필요한가, “시간까지” 필요한가?
• 이 값은 “언제 발생했는지(이벤트 시각)”인가, “언제 기록했는지(저장 시각)”인가?
• 타임존을 어떻게 통일할 것인가(서버/DB/앱 전부 같은 기준인가)?

실무에서 자주 쓰는 패턴

• created_at: 행이 생성된 시간(기본값으로 현재 시각)
• updated_at: 행이 수정된 시간(트리거/애플리케이션 로직으로 갱신)
• status_updated_at: 배송 상태가 마지막으로 바뀐 시간(업무 이벤트에 대응)

핵심은 “같은 의미의 컬럼은 전 테이블에서 이름과 타입을 통일”하는 것입니다. 그래야 나중에 조인/리포트/배치 작업이 쉬워집니다.

기본값(DEFAULT)과 NULL 정책

DEFAULT와 NULL은 “데이터가 비어 있을 때 어떻게 할 것인가”를 DB 레벨에서 결정합니다.

NULL이 나쁜가요?

• NULL은 “값이 아직 없다/모른다”라는 의미를 표현하는 합법적인 방법입니다.
• 다만 NULL이 많아지면 쿼리 조건이 복잡해지고(IS NULL 처리), 집계/정렬에서 예상치 못한 결과가 나오기 쉽습니다.
• 규칙이 정해져 있는 값(예: 상태, 생성 시각)은 NOT NULL과 DEFAULT를 함께 쓰는 편이 관리가 쉽습니다.

배송 상태 관리에서 추천하는 기본값 정책

컬럼	추천	이유
status_code	NOT NULL + DEFAULT	초기 상태를 강제해 누락을 방지
created_at	NOT NULL + 현재 시각 기본값	누가 넣어도 생성 시각이 비지 않게
updated_at	정책 통일(트리거/앱)	자동 갱신 방식이 DB별로 다를 수 있음
delivered_at	NULL 허용	배송 완료 전에는 값이 없어야 자연스러움

실습: “배송 상태 관리” 테이블 설계

실습에서는 다음 요구사항을 만족하는 설계를 목표로 합니다.

• 배송(Shipment) 한 건은 현재 상태(status_code)를 가진다.
• 상태는 여러 단계로 바뀌며, 바뀐 이력(히스토리)을 남길 수 있다.
• 상태 값은 오타/임의 값이 들어가지 않도록 “관리 가능한 구조”로 만든다.

1) 상태 코드는 “룩업 테이블 + FK” 방식이 관리가 쉽습니다

초보자 관점에서 가장 안전한 방식은 “상태 코드 목록 테이블을 만들고, 배송 테이블에서 외래키로 참조”하는 것입니다. 이렇게 하면 상태 값이 오타로 들어갈 가능성이 크게 줄고, 상태명(한글 표시명), 종료 상태 여부 같은 메타 정보도 함께 관리할 수 있습니다.

-- 1) 배송 상태 코드(룩업) 테이블
CREATE TABLE shipping_status_codes (
  status_code   VARCHAR(30) PRIMARY KEY,
  name_kr       VARCHAR(50) NOT NULL,
  is_terminal   CHAR(1) NOT NULL DEFAULT 'N',  -- 종료 상태(Y/N)
  sort_order    INT NOT NULL DEFAULT 0
);

INSERT INTO shipping_status_codes(status_code, name_kr, is_terminal, sort_order) VALUES
('PENDING',     '배송준비',     'N', 10),
('PICKED',      '출고완료',     'N', 20),
('SHIPPED',     '배송시작',     'N', 30),
('IN_TRANSIT',  '배송중',       'N', 40),
('DELIVERED',   '배송완료',     'Y', 50),
('RETURNED',    '반품완료',     'Y', 60),
('CANCELED',    '배송취소',     'Y', 70);

2) 현재 상태를 가지는 shipments 테이블

배송 한 건을 표현하는 테이블입니다. 실습에서는 주문과의 관계를 단순화하기 위해 order_id만 두고 진행합니다.

-- 2) 배송(Shipment) 테이블
CREATE TABLE shipments (
  shipment_id        BIGINT PRIMARY KEY,
  order_id           BIGINT NOT NULL,
  carrier_name       VARCHAR(50) NOT NULL,       -- 택배사명
  tracking_number    VARCHAR(50) NOT NULL,       -- 운송장 번호
  status_code        VARCHAR(30) NOT NULL DEFAULT 'PENDING',
  status_updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  delivered_at       TIMESTAMP NULL,
  created_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_shipments_status
    FOREIGN KEY (status_code) REFERENCES shipping_status_codes(status_code)
);

-- 조회 최적화를 위한 인덱스 예시
CREATE INDEX idx_shipments_order_id ON shipments(order_id);
CREATE INDEX idx_shipments_status_code ON shipments(status_code);

3) 상태 변경 이력 테이블(히스토리)

현재 상태만 저장하면 “언제 어떤 순서로 바뀌었는지”를 잃기 쉽습니다. 상태 이력 테이블을 두면 CS 대응이나 분석(지연 원인 분석 등)에 유리합니다.

-- 3) 배송 상태 변경 이력
CREATE TABLE shipment_status_events (
  event_id      BIGINT PRIMARY KEY,
  shipment_id   BIGINT NOT NULL,
  status_code   VARCHAR(30) NOT NULL,
  event_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  note          VARCHAR(200) NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_events_shipment
    FOREIGN KEY (shipment_id) REFERENCES shipments(shipment_id),

  CONSTRAINT fk_events_status
    FOREIGN KEY (status_code) REFERENCES shipping_status_codes(status_code)
);

CREATE INDEX idx_events_shipment_id_event_at ON shipment_status_events(shipment_id, event_at);

4) 상태 변경은 “현재 상태 업데이트 + 이력 INSERT”를 함께 처리합니다

실무에서는 상태 변경 시점에 “현재 상태”와 “이력”이 불일치하면 곤란합니다. 따라서 보통 두 작업을 한 트랜잭션으로 묶습니다(트랜잭션/잠금은 10회차 내용과 자연스럽게 연결됩니다).

-- 상태를 'DELIVERED'로 변경하고, delivered_at을 함께 기록하는 예시
START TRANSACTION;

UPDATE shipments
SET status_code = 'DELIVERED',
    status_updated_at = CURRENT_TIMESTAMP,
    delivered_at = CURRENT_TIMESTAMP,
    updated_at = CURRENT_TIMESTAMP
WHERE shipment_id = 10001;

INSERT INTO shipment_status_events(event_id, shipment_id, status_code, note)
VALUES (90001, 10001, 'DELIVERED', '수취인 배송 완료 확인');

COMMIT;

5) ALTER로 요구사항 변화에 대응하기

운영을 하다 보면 컬럼이 추가되거나 타입을 늘려야 하는 일이 생깁니다. 아래는 초보자가 자주 만나는 ALTER 예시입니다(DB에 따라 문법이 조금 다를 수 있습니다).

-- (예시 1) 배송 요청사항 컬럼 추가(길이가 짧고 변동적이므로 VARCHAR)
ALTER TABLE shipments
ADD COLUMN delivery_request VARCHAR(100) NULL;

-- (예시 2) tracking_number 길이를 늘려야 하는 경우
-- DB에 따라 ALTER/MODIFY 문법이 다릅니다.
ALTER TABLE shipments
ALTER COLUMN tracking_number TYPE VARCHAR(80);

-- (예시 3) 기본값 변경(초기 상태 정책을 바꾸는 경우)
ALTER TABLE shipments
ALTER COLUMN status_code SET DEFAULT 'PENDING';

6) 설계 검증용 조회(간단 점검)

• 현재 배송 상태와 한글 상태명을 함께 보기
• 배송 한 건의 상태 변경 이력을 시간순으로 보기

-- 현재 상태 + 표시명 조인
SELECT s.shipment_id, s.order_id, s.status_code, c.name_kr, s.status_updated_at
FROM shipments s
JOIN shipping_status_codes c ON c.status_code = s.status_code
WHERE s.shipment_id = 10001;

-- 상태 이력 조회
SELECT e.shipment_id, e.status_code, c.name_kr, e.event_at, e.note
FROM shipment_status_events e
JOIN shipping_status_codes c ON c.status_code = e.status_code
WHERE e.shipment_id = 10001
ORDER BY e.event_at ASC;

실행 단계 체크리스트

• 요구사항을 문장으로 고정합니다(예: “배송은 현재 상태가 있고, 상태 이력이 필요하다”).
• 상태 값은 자유 문자열이 아니라 “코드 테이블 + FK”로 관리할지 먼저 결정합니다.
• 문자 타입은 코드/이름/번호/메모를 구분해 CHAR/VARCHAR/TEXT를 고릅니다.
• 날짜/시간 컬럼은 의미를 분리합니다(created_at, updated_at, status_updated_at, delivered_at 등).
• NOT NULL과 DEFAULT를 “누락되면 곤란한 값”에 적용합니다(초기 상태, 생성 시각 등).
• 조회 패턴을 기준으로 인덱스를 최소 구성합니다(주문 조회, 상태별 목록, 이력 조회 등).
• 상태 변경은 “현재 상태 업데이트 + 이력 INSERT”를 트랜잭션으로 묶어 불일치를 방지합니다.
• ALTER는 운영 비용이 될 수 있으므로, 영향 범위(데이터량/잠금)를 생각하고 작게 진행합니다.

추가로 생각해볼 점

• 상태 코드의 변화 관리: 상태 단계가 늘어나거나 이름이 바뀔 수 있습니다. 코드 테이블을 쓰면 변경을 중앙에서 관리하기 쉬워집니다.
• 종료 상태(is_terminal): “배송완료/반품완료/취소”처럼 더 이상 진행되지 않는 상태를 표시해두면, 화면/배치/알림 로직이 단순해집니다.
• 이력 테이블의 크기: 이벤트가 쌓이면 커집니다. 보관 정책(기간/아카이빙)이나 파티셔닝 같은 운영 전략을 장기적으로 고려할 수 있습니다.
• updated_at 자동 갱신: DB 트리거로 할지, 애플리케이션에서 책임질지 팀 기준을 정해두면 혼란이 줄어듭니다.
• 유니크 제약: 같은 택배사에서 운송장 번호가 중복되지 않는 정책이라면 (carrier_name, tracking_number)에 유니크 제약을 검토할 수 있습니다.

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.