데이터 타입과 제약조건: 포인트 시스템에서 “틀린 데이터”는 애초에 못 들어오게 막아야 한다

포인트 시스템을 운영하다 보면, 조회 쿼리를 아무리 잘 만들어도 해결되지 않는 문제가 있습니다. 바로 데이터 자체가 잘못 들어오는 경우입니다. 한 번 잘못 적립되거나 중복 차감이 발생하면, 그 이후부터는 “정상 데이터 위에 예외 처리를 덧칠하는 작업”이 계속 늘어납니다. 반대로, 초기에 데이터 타입과 제약조건을 제대로 잡아두면 많은 문제가 입력 단계에서 차단됩니다.

이 단원의 목적: SQL을 “조회 언어”가 아니라 “데이터 품질 언어”로 쓰기

SQL을 배우는 초반에는 SELECT/WHERE가 눈에 들어오지만, 실제 서비스에서는 스키마 설계(타입/제약조건)가 시스템의 신뢰도를 좌우합니다. 이번 글의 목표는 다음 3가지입니다.

  1. 포인트 시스템에 맞는 타입을 고르고, 오버플로우/범위 문제를 예방한다
  2. NOT NULL, DEFAULT, CHECK(가능한 DBMS 한정)로 “불완전한 입력”을 막는다
  3. UNIQUE/FK로 “중복 적립”과 “없는 사용자에 대한 적립” 같은 사고를 차단한다

1) 숫자 타입: INT로 충분한가, BIGINT가 필요한가

포인트는 결국 숫자입니다. 그런데 숫자는 시간이 지나면서 쌓이고, 서비스가 커지면 “상상보다 빨리” 범위를 넘길 수 있습니다. 그래서 초기에 아래를 구분해서 생각하는 편이 안전합니다.

  • amount(변동량): 한 번의 적립/차감 크기
  • balance(잔액): 누적 결과
  • id(일련번호): 내역이 쌓이는 속도에 따라 매우 빠르게 증가

amount는 INT가 흔히 충분하지만, balance는 BIGINT가 안전하다

단건 적립이 수천~수만 포인트 수준이라면 amount는 INT로 충분한 경우가 많습니다. 하지만 balance는 장기간 누적될 수 있고, 이벤트나 정책 변경으로 규모가 커질 수 있습니다. 그래서 잔액(balance)을 별도 테이블에 둔다면 BIGINT를 권장하는 이유가 여기에 있습니다. 

-- 내역 일련번호는 빠르게 늘어날 수 있으므로 BIGINT 계열이 흔히 쓰입니다. id BIGINT UNSIGNED -- 단건 변동량: INT amount INT NOT NULL -- 잔액: BIGINT (누적이므로) balance BIGINT NOT NULL

2) 문자열 타입: uid/ref_id는 “길이와 규칙”이 중요하다

포인트 시스템에서 문자열은 단순 텍스트가 아니라 식별자입니다. 식별자는 “길이/형식이 일정할수록” 안정적입니다.

uid: UUID를 쓸 때 CHAR(36) vs BINARY(16)

UUID를 문자열로 저장하면 보기가 편하지만(디버깅에 유리), 공간과 인덱스 측면에서는 BINARY(16)이 더 효율적인 편입니다. 다만 학습 단계에서는 가독성이 중요하니 CHAR(36)로 시작해도 충분합니다. “왜 BINARY(16)이 빠른가” 같은 최적화는 뒤에서 다뤄도 늦지 않습니다. 

uid CHAR(36) NOT NULL

ref_id: 중복 방지의 핵심이므로 “최대 길이”를 과하게 키우지 않는다

ref_id는 주문번호/이벤트ID/요청ID 같은 값을 저장합니다. 여기서 VARCHAR(255)로 넉넉히 잡아도 되지만, 인덱스를 걸 계획이라면 길이는 조심해야 합니다. 그래서 처음엔 VARCHAR(64) 정도로 시작하는 경우가 많습니다. 

ref_id VARCHAR(64) NULL

3) ENUM은 편하다. 하지만 “정책 변경”을 고려해야 한다

point_type(FREE/PAID), action_type(CHARGE/USE/...) 같은 값은 ENUM으로 정의하면 잘못된 문자열이 들어오는 것을 막을 수 있고, 데이터가 읽기 쉬워집니다. 다만 ENUM은 나중에 값이 추가/변경될 때 DDL 변경이 필요할 수 있으니, 정책이 자주 바뀌는 서비스라면 코드 테이블로 분리하는 선택지도 있습니다.

이 연재에서는 학습과 이해를 위해 ENUM으로 시작합니다. 대신 “ENUM을 선택했을 때의 제약”을 명확히 인지하는 것이 중요합니다. 

point_type ENUM('PAID','FREE') NOT NULL action_type ENUM('CHARGE','USE','REFUND','USE_CANCEL','EXPIRE','ADJUST') NOT NULL

4) NOT NULL과 DEFAULT: 빈 값이 들어오는 순간, 운영이 흔들린다

운영 중 데이터 품질을 깨뜨리는 가장 흔한 원인은 “어느 순간 들어온 NULL”입니다. 예를 들어 created_at이 NULL이면 기간 조회가 망가지고, amount가 NULL이면 잔액 합산이 불가능합니다. 그래서 포인트 원장에서는 핵심 컬럼에 NOT NULL을 강하게 거는 편이 좋습니다. 

amount INT NOT NULL created_at DATETIME NOT NULL

DEFAULT는 “입력 실수”를 줄이는 데 도움이 됩니다. 다만 DEFAULT를 남발하면 “왜 이 값이 들어갔는지” 모호해지기도 합니다. 그래서 포인트 원장처럼 중요한 데이터는 DEFAULT를 최소화하고, 필요한 곳(예: balance 기본값 0)만 정확히 쓰는 편이 안전합니다. 

balance BIGINT NOT NULL DEFAULT 0

5) CHECK 제약조건: DBMS에 따라 사용 가능 범위가 다르다

CHECK는 “값의 범위/규칙”을 강제합니다. 예를 들어 amount가 0이 되면 안 된다거나, 특정 action_type에는 amount가 음수여야 한다는 규칙을 만들 수 있습니다.

다만 CHECK는 DBMS/버전에 따라 제약이 다를 수 있으므로, 실제 운영 환경에서 지원 여부를 확인한 뒤 적용하는 편이 좋습니다. 여기서는 개념을 이해하기 위한 예시만 다룹니다. 

-- 예시(지원되는 DBMS에서): amount는 0이 아니어야 한다 CHECK (amount <> 0)

포인트 시스템에서는 더 강한 규칙이 필요할 수 있습니다. 예를 들어 USE/EXPIRE는 음수, CHARGE는 양수 같은 규칙이죠. 하지만 이런 규칙은 예외(관리자 보정, 취소 처리 방식) 때문에 변형될 수 있습니다. 그래서 초반에는 CHECK를 과도하게 걸기보다, 핵심 제약(필수값/중복방지/참조 무결성)을 먼저 단단히 하는 것이 우선입니다.

6) UNIQUE: “중복 적립” 사고를 DB가 막게 하라

중복 적립은 생각보다 쉽게 발생합니다. 네트워크 재시도, 사용자의 중복 클릭, 서버 타임아웃 후 재호출 같은 상황이 대표적입니다. 애플리케이션 코드로 방어할 수도 있지만, 가장 강력한 마지막 방어선은 DB의 UNIQUE입니다.

UNIQUE를 어디에 걸 것인가: (uid, ref_id)가 흔한 선택

UNIQUE KEY uk_uid_ref (uid, ref_id)

이 제약조건이 의미를 가지려면 ref_id가 “중복 방지에 쓸 수 있을 만큼 신뢰 가능한 값”이어야 합니다. 주문번호, 결제 트랜잭션 ID, 이벤트 지급 ID처럼 시스템이 유일성을 보장할 수 있는 값을 쓰는 것이 좋습니다.

반대로 ref_id가 NULL이거나(정책상 있을 수도 없음), 이벤트 성격상 유일성이 모호하다면 UNIQUE는 오히려 장애를 만들 수 있습니다. 이런 경우는 “ref_id를 만들 규칙”부터 정하고, 그 다음 UNIQUE를 적용하는 순서가 안전합니다.

7) FOREIGN KEY: “없는 사용자에게 적립” 같은 오류를 막는다

포인트 내역은 반드시 특정 사용자(uid)에 귀속됩니다. 그런데 운영에서는 삭제/탈퇴/마이그레이션 과정에서 “users에는 없는 uid인데 point_history에만 남는” 데이터가 생기기도 합니다. 이런 데이터는 이후 조인(JOIN)이나 리포트에서 빠지며, 조용히 문제를 키웁니다. 

CONSTRAINT fk_point_history_user FOREIGN KEY (uid) REFERENCES users(uid)

FK는 데이터 무결성을 지켜주는 대신, 대량 입력이나 마이그레이션 상황에서는 속도/유연성이 문제가 될 수 있습니다. 그래서 시스템 특성에 따라 선택이 갈리지만, 학습 단계에서는 “참조 무결성”이라는 개념을 확실히 잡아두는 것이 도움이 됩니다.

8) 최종 스키마 예시: 지금 단계에서의 현실적인 균형

지금까지의 내용을 반영한 point_history는 아래처럼 정리할 수 있습니다. 이 스키마는 “학습용”이면서도 운영에서 자주 쓰는 안전장치를 포함합니다. 

CREATE TABLE point_history ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, uid CHAR(36) NOT NULL, point_type ENUM('PAID','FREE') NOT NULL, action_type ENUM('CHARGE','USE','REFUND','USE_CANCEL','EXPIRE','ADJUST') NOT NULL, amount INT NOT NULL, ref_id VARCHAR(64) NULL, memo VARCHAR(255) NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), INDEX idx_uid_created (uid, created_at), INDEX idx_ref (ref_id), UNIQUE KEY uk_uid_ref (uid, ref_id), CONSTRAINT fk_point_history_user FOREIGN KEY (uid) REFERENCES users(uid) );

간단 검증: 제약조건이 실제로 ‘사고’를 막는지 확인하기

아래는 일부러 중복 내역을 넣어보는 테스트입니다. UNIQUE가 제대로 동작한다면 두 번째 INSERT는 실패해야 합니다. 이런 검증 절차를 글에 포함하면 “따라할 수 있는 콘텐츠”가 되어 품질이 올라갑니다. 

SET @uid := '11111111-1111-1111-1111-111111111111'; -- 첫 번째는 성공 INSERT INTO point_history (uid, point_type, action_type, amount, ref_id, created_at) VALUES (@uid, 'FREE', 'CHARGE', 100, 'EVENT_DUP_TEST', NOW()); -- 두 번째는 (uid, ref_id) 중복이므로 실패해야 정상 INSERT INTO point_history (uid, point_type, action_type, amount, ref_id, created_at) VALUES (@uid, 'FREE', 'CHARGE', 100, 'EVENT_DUP_TEST', NOW());

만약 실제 운영에서 “중복 적립이 일어나도 괜찮다”는 요구가 있다면, 그건 보통 “중복 적립을 허용”하는 게 아니라 “어떤 경우를 같은 이벤트로 볼 것인지”가 정의되지 않은 상태일 확률이 높습니다. 그래서 포인트 시스템은 정책 정의(업무 규칙)와 스키마(제약조건)가 함께 가야 합니다.

다음 글 예고: PRIMARY KEY/UNIQUE로 ‘중복 적립’과 ‘중복 차감’을 더 단단하게 막는 키 설계

다음 글에서는 키 설계를 좀 더 깊게 다룹니다. 단순히 “PK는 id” 같은 수준이 아니라, 실제 포인트 시스템에서 자주 등장하는 요구(요청 재시도, 멱등성, 트랜잭션 추적)에 맞춰 어떤 키를 어떻게 잡아야 운영이 편해지는지 정리합니다.

간단 검증: 이번 글이 ‘가치 있는 콘텐츠’가 되는 이유

단순히 타입을 나열하지 않고, 포인트 시스템에서 실제로 발생하는 사고(중복 적립, 없는 사용자, NULL 유입)를 기준으로 “어떤 제약조건이 왜 필요한지”를 연결했습니다. 또한 따라할 수 있는 스키마와 테스트까지 제공해, 읽는 사람이 결과를 확인할 수 있는 구조로 만들었습니다.

댓글

댓글 쓰기

이 블로그의 인기 게시물

JOIN 기초: users와 point_history를 합쳐 ‘회원별 요약(잔액/최근 활동)’ 만들기

포인트 원장(point_history)만으로도 잔액과 통계를 만들 수 있지만, 실제 화면이나 운영 도구에서는 거의 항상 “사용자 정보”가 함께 필요합니다. 예를 들어 이런 질문들은 point_history만으로는 답이 부족합니다. 이 uid가 누구인지(가입일, 상태 등) 함께 보고 싶다 회원 목록을 보면서 각 회원의 잔액/최근 활동을 한 줄로 요약하고 싶다 최근 7일 동안 사용이 많은 회원을 “회원 정보와 함께” 추려 보고 싶다 이때 필요한 것이 JOIN입니다. JOIN은 테이블을 “합치는” 기능이지만, 정확히는 두 테이블의 행을 연결 해 새로운 결과를 만드는 방식입니다. 이번 글에서는 포인트 시스템에서 가장 흔한 JOIN 패턴(회원 + 원장 요약)을 중심으로, INNER JOIN/LEFT JOIN의 차이와 실전에서 헷갈리는 포인트를 정리합니다. 이 단원의 목적: “회원 목록 + 포인트 요약”을 안정적으로 뽑아내기 JOIN은 문법을 외우는 것보다 “어떤 결과를 만들고 싶은지”를 먼저 그리는 것이 중요합니다. 이번 글의 목표는 아래 3가지입니다. INNER JOIN과 LEFT JOIN의 차이를 이해하고 상황에 맞게 선택한다 집계(GROUP BY)와 JOIN을 결합해 ‘회원별 잔액/통계’를 만든다 JOIN에서 자주 발생하는 오류(행 폭증, 중복 집계)를 피하는 패턴을 익힌다 준비: users에 최소한의 컬럼을 더해 “JOIN 결과가 읽히게” 만들기 이전 글에서는 users가 uid와 created_at만 있었지만, JOIN 결과를 보기 편하게 하려면 display_name 같은 컬럼이 하나 있으면 좋습니다. 실제 서비스라면 이메일/닉네임/상태값이 있을 텐데, 여기서는 학습 목적상 간단히 display_name을 추가합니다. -- 이미 있다면 생략 ALTER TABLE users ADD COLUMN display_name VARCHAR(50) NULL; -- 예시 값 입력(학습용) UPDATE users SET display_n...
자세한 내용 보기

점검 SQL: “원장 합계(SUM) vs balance” 불일치를 찾아내고 원인을 좁히는 방법

포인트 시스템은 “정상 처리”만 잘 만들면 끝나는 것처럼 보이지만, 실제로는 시간이 지날수록 다양한 이유로 데이터가 어긋날 수 있습니다. 예를 들어 이런 상황이 생깁니다. 중간 장애로 트랜잭션이 일부만 반영된 것처럼 보이는 케이스(재처리/수동 개입 포함) 운영 정정(ADJUST)이나 소프트 삭제(hide) 처리 기준이 흔들린 케이스 배치 작업(만료/정산)이 예상과 다른 시간에 들어간 케이스 코드 변경 이후 특정 action_type이 누락/중복 집계된 케이스 그래서 “점검 쿼리”는 기능 쿼리만큼 중요합니다. 특히 balance 테이블(잔액 캐시)을 함께 쓰는 구조라면, 주기적으로 원장 합계와 balance가 같은지 확인해야 합니다. 이 단원의 목적: 불일치를 “발견”하고 “원인”을 좁힐 수 있는 쿼리 세트 만들기 이번 글에서는 아래 3가지를 목표로 합니다. 원장 합계(SUM)와 balance를 비교해 불일치 UID를 뽑는다 불일치가 발견되면 어디서부터 어긋났는지 범위를 좁힌다 자주 발생하는 유형(숨김 처리, 중복 요청, 배치 만료)을 점검 항목으로 만든다 1) 1차 점검: uid별 “원장 잔액”과 “balance 잔액” 비교 가장 기본은 uid별로 두 값을 나란히 놓고 비교하는 것입니다. 여기서는 hide=0만 원장 합계에 포함한다고 가정합니다. SELECT b.uid, b.balance AS balance_cache, COALESCE(l.ledger_balance, 0) AS balance_ledger, b.balance - COALESCE(l.ledger_balance, 0) AS diff FROM point_balance b LEFT JOIN ( SELECT uid, SUM(amount) AS ledger_balance FROM point_history WHERE hide = 0 GROUP BY uid ) l ON l.uid = b.uid WHERE b.balance <> COALESCE(l.ledger_...
자세한 내용 보기

EXPLAIN 기초: 점검/리포트 쿼리가 느려질 때 “왜 느린지” 확인하는 방법

포인트 시스템을 만들어가면서 새삼 느끼는것은 생성형AI가 있어서 설계하고 구상하는게 너무 쉬워졌다는것입니다. 혼자서 했다면 아마도 어려움이 많았을거 같은데 AI와 대화를 하며 개선점을 찾아가는 방식으로 진행하다보니 생각보다 기능이 치밀하게 설계를 할 수 있었습니다. 지금까지 하나하나 학습범위를 늘려가며 왔는데 아직도 갈길이 먼거 같습니다.  포인트 원장(point_history)은 시간이 지날수록 커집니다. 데이터가 커지면, 기능 쿼리(내역/잔액)뿐 아니라 점검 쿼리(원장 vs balance 비교, 월별 집계, ref_id 그룹핑)도 점점 느려집니다. 이때 흔히 “인덱스를 더 만들면 되지 않을까?”라고 생각하지만, 인덱스는 무조건 늘린다고 좋아지지 않습니다. 어떤 쿼리가 어떤 방식으로 실행되는지 모르면 잘못된 인덱스를 만들거나, 효과 없는 튜닝을 반복할 가능성이 큽니다. 그래서 필요한 도구가 EXPLAIN 입니다. EXPLAIN은 “이 쿼리를 DB가 어떤 순서로 실행할지”를 보여주는 실행 계획입니다. 즉, 느린 이유를 추측하지 않고 눈으로 확인 할 수 있게 해줍니다. 이 단원의 목적: 실행 계획을 보고 “어디를 고칠지” 결정하기 이번 글의 목표는 아래 3가지입니다. EXPLAIN 결과에서 최소한의 핵심 항목을 읽을 수 있게 된다 포인트 시스템 쿼리에서 자주 나오는 느린 패턴(풀 스캔, filesort)을 구분한다 인덱스를 “조회 패턴에 맞게” 설계하는 감각을 만든다 1) EXPLAIN 사용법: SELECT 앞에 붙이면 된다 가장 기본은 SELECT 앞에 EXPLAIN을 붙이는 것입니다. EXPLAIN SELECT id, action_type, amount, created_at FROM point_history WHERE uid = '11111111-1111-1111-1111-111111111111' AND hide = 0 ORDER BY created_at DESC, id DESC LIMIT 50; 실행 결과는 ...
자세한 내용 보기