Site Review - Phase 3 놀고팜 + 고향사랑기부 통합 리뉴얼

물리 데이터베이스 설계 및 DDL

💾 PostgreSQL 15+ 규격 DDL 🔑 15자리 계층 카테고리 & G-Point 지갑 구축 ⚡ 대용량 트래픽 대비 고속 쿼리 인덱스 최적화

본 설계는 기존 놀고팜의 구/신 테이블 혼재(`product` / `productOld`) 부채를 청산하고, 체험/숙소/기부를 아우르는 정규화된 PostgreSQL 물리 DDL을 제시합니다. 파일 원본은 [db-schema.sql](./db-schema.sql)로 저장되어 있습니다.

1. 설계된 주요 DB 테이블 목록

테이블명 도메인 분류 기능 및 관계 설명 비즈니스 제약 사항 및 검증 요건
`users` 공통 / SSO 통합 회원 정보, PASS 인증 CI값, 거주지 법정동 코드 보관 `ci_value` UNIQUE 제약 (이중 가입 제한), 거주지 주소 코드 검증
`common_categories` 공통 / 이커머스 15자리 문자열 계층 코드 체계 (대-중-소-세분류) 하위 분류 고속 조회 (`LIKE '001%'`) 최적화 적용
`travel_products` 농촌 관광 체험(`EXPERIENCE`), 숙소(`ACCOMMODATION`), 캠핑(`CAMPING`) 마스터 도메인별 파편화를 단일 테이블 구조로 통합
`local_governments` 고향사랑기부 243개 지자체 법정동 코드 및 e음 API 연동 매핑 키 개별 위탁 계약 지자체 활성 상태 (`is_active: Y/N`) 필터링
`user_wallets` G-Point 지갑 회원별 적립 G-Point 잔액 및 누적 통계 포인트 동시 차감 제어를 위한 `SELECT FOR UPDATE` 배타락 대상
`orders` / `order_items` 통합 주문 일반 PG 결제액과 기부 수납액이 복합 결제된 통합 결제 원장 Saga 트랜잭션 관리용 상태 값 (`PENDING`, `COMPLETED`, `CANCELLED`)

2. 대량 데이터 튜닝을 위한 튜닝 인덱스 (CREATE INDEX)

⚡ 1. 본인인증 고속 매핑 Index

기부 진입 단계에서 e음 API 망과 연계해 중복 가입이나 기부 자격을 체크하기 위해 CI 필드에 부분 인덱스를 할당합니다.

CREATE INDEX idx_users_ci 
ON users (ci_value) 
WHERE ci_value IS NOT NULL;
⚡ 2. 추천 여행 피드 노출 최적화

Next.js 홈 화면의 큐레이션 카드 피드를 로드할 때 최신 추천 액티비티를 가져오는 고속 쿼리 인덱스입니다.

CREATE INDEX idx_products_feed 
ON travel_products 
(product_type, is_recommend, status);

3. DDL 소스 스크립트 프리뷰

-- G-Point 지갑 및 적립 상세 테이블 DDL
CREATE TABLE user_wallets (
    wallet_idx SERIAL PRIMARY KEY,
    user_idx INT NOT NULL UNIQUE,
    current_gpoint INT NOT NULL DEFAULT 0,
    accumulated_earn INT NOT NULL DEFAULT 0,
    accumulated_use INT NOT NULL DEFAULT 0,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    FOREIGN KEY (user_idx) REFERENCES users(user_idx) ON DELETE CASCADE
);

CREATE TABLE point_transactions (
    transaction_idx SERIAL PRIMARY KEY,
    user_idx INT NOT NULL,
    amount INT NOT NULL,
    transaction_type VARCHAR(30) NOT NULL, -- EARN_DONATION, USE_TRAVEL_RESERVATION 등
    transaction_hash VARCHAR(64) NOT NULL, -- 해시 기반 위변조 추적
    reference_idx INT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);
📑 작성 AI