Fresh-Shop

Tables (28)

address

  • address_id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → member.member_id — NOT NULL
  • recipient — VARCHAR(50) — NOT NULL
  • phone_enc — VARBINARY(255) — NOT NULL
  • zipcode — VARCHAR(10) — NOT NULL
  • road_address — VARCHAR(255) — NOT NULL — 도로명 주소
  • detail_address — VARCHAR(255)
  • is_default — TINYINT(1) — NOT NULL — 기본 배송지

admin

  • admin_id — BIGINT — Primary key — NOT NULL
  • login_id — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • role — ENUM('SUPER_ADMIN', 'ADMIN') — NOT NULL — RBAC 권한
  • created_at — DATETIME — NOT NULL

audit_log

  • log_id — BIGINT — Primary key — NOT NULL
  • admin_id — BIGINT — Foreign key → admin.admin_id — NOT NULL
  • action — VARCHAR(50) — NOT NULL — PRODUCT_DELETE/REFUND/GRADE_CHANGE 등
  • target — VARCHAR(100)
  • detail — TEXT
  • created_at — DATETIME — NOT NULL

cart

  • cart_id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → member.member_id — NOT NULL
  • updated_at — DATETIME — NOT NULL

cart_item

  • cart_item_id — BIGINT — Primary key — NOT NULL
  • cart_id — BIGINT — Foreign key → cart.cart_id — NOT NULL
  • product_id — BIGINT — Foreign key → product.product_id — NOT NULL
  • qty — INT — NOT NULL
  • added_at — DATETIME — NOT NULL

category

  • category_id — BIGINT — Primary key — NOT NULL
  • parent_id — BIGINT — Foreign key → category.category_id — 상위 카테고리(확장용)
  • name — VARCHAR(50) — NOT NULL — 해산물/육류/채소/과일

claim

  • claim_id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.order_id — NOT NULL
  • type — ENUM('CANCEL', 'RETURN', 'EXCHANGE') — NOT NULL
  • status — ENUM('REQUESTED', 'APPROVED', 'REJECTED', 'COMPLETED') — NOT NULL
  • reason_type — ENUM('CHANGE_OF_MIND', 'DEFECT')
  • reason — VARCHAR(500)
  • processed_by — BIGINT — Foreign key → admin.admin_id — admin (NULL=시스템 자동)
  • requested_at — DATETIME — NOT NULL
  • processed_at — DATETIME

claim_item

  • claim_item_id — BIGINT — Primary key — NOT NULL
  • claim_id — BIGINT — Foreign key → claim.claim_id — NOT NULL
  • order_item_id — BIGINT — Foreign key → order_item.order_item_id — NOT NULL
  • qty — INT — NOT NULL — 부분 처리 수량

coupon

  • coupon_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • discount_type — ENUM('AMOUNT', 'RATE') — NOT NULL
  • discount_value — INT — NOT NULL
  • min_order_amount — INT — NOT NULL — 사용조건
  • target_grade_id — BIGINT — Foreign key → member_grade.grade_id — 대상 등급(NULL=전체)
  • valid_from — DATE — NOT NULL
  • valid_to — DATE — NOT NULL

member

  • member_id — BIGINT — Primary key — NOT NULL
  • login_id — VARCHAR(16) — NOT NULL — 영문소문자+숫자 4~16
  • password_hash — VARCHAR(255) — NOT NULL — BCrypt 단방향 해시
  • name_enc — VARBINARY(255) — NOT NULL — 이름(암호화)
  • nickname — VARCHAR(50) — NOT NULL
  • email_enc — VARBINARY(255) — NOT NULL — 이메일(암호화)
  • phone_enc — VARBINARY(255) — NOT NULL — 휴대전화(암호화)
  • grade_id — BIGINT — Foreign key → member_grade.grade_id — NOT NULL
  • marketing_agreed — TINYINT(1) — NOT NULL
  • status — ENUM('ACTIVE', 'BLOCKED', 'WITHDRAWN') — NOT NULL
  • deleted_at — DATETIME — 소프트딜리트
  • created_at — DATETIME — NOT NULL

member_coupon

  • member_coupon_id — BIGINT — Primary key — NOT NULL
  • coupon_id — BIGINT — Foreign key → coupon.coupon_id — NOT NULL
  • member_id — BIGINT — Foreign key → member.member_id — NOT NULL
  • used_order_id — BIGINT — Foreign key → orders.order_id
  • status — ENUM('ISSUED','USED','EXPIRED') — NOT NULL
  • issued_at — DATETIME — NOT NULL
  • used_at — DATETIME

member_grade

  • grade_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(50) — NOT NULL — 등급명 (기본/단골 등)
  • discount_rate — DECIMAL(5, 2) — NOT NULL — 등급 할인율(%)
  • promotion_rule — VARCHAR(255) — 승급 기준

notification

  • notification_id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → member.member_id — NOT NULL
  • channel — ENUM('EMAIL','SMS','APP') — NOT NULL
  • type — ENUM('QNA_ANSWER','ORDER_STATUS','SHIPPING','EXPIRY') — NOT NULL
  • content — TEXT — NOT NULL
  • status — ENUM('SENT','FAILED','RETRY') — NOT NULL
  • created_at — DATETIME — NOT NULL

order_item

  • order_item_id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.order_id — NOT NULL
  • product_id — BIGINT — Foreign key → product.product_id — NOT NULL
  • name_snapshot — VARCHAR(255) — NOT NULL — 주문시점 상품명
  • unit_price — INT — NOT NULL — 주문시점 가격
  • qty — INT — NOT NULL
  • item_status — ENUM('ORDERED', 'CANCELED', 'RETURN_REQ', 'RETURNED', 'EXCHANGE_REQ', 'EXCHANGED') — NOT NULL

order_status_history

  • history_id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.order_id — NOT NULL
  • from_status — VARCHAR(30)
  • to_status — VARCHAR(30) — NOT NULL
  • changed_by — ENUM('USER','ADMIN','BATCH') — NOT NULL
  • changed_at — DATETIME — NOT NULL

orders

  • order_id — BIGINT — Primary key — NOT NULL
  • order_no — VARCHAR(30) — NOT NULL — 주문번호
  • member_id — BIGINT — Foreign key → member.member_id — NOT NULL
  • status — ENUM('PAYMENT_PENDING', 'PAID', 'PRODUCT_PREPARING', 'SHIPMENT_PREPARING', 'SHIPPING', 'DELIVERED', 'CONFIRMED', 'RETURN_REQUESTED', 'RETURNED', 'CANCELED') — NOT NULL
  • product_amount — INT — NOT NULL — 총상품금액
  • discount_amount — INT — NOT NULL — 쿠폰+등급+포인트
  • shipping_fee — INT — NOT NULL — 배송비
  • total_amount — INT — NOT NULL — 최종결제금액
  • earned_point — INT — NOT NULL — 적립예정포인트
  • ship_recipient — VARCHAR(50) — NOT NULL — 배송지 스냅샷
  • ship_phone_enc — VARBINARY(255) — NOT NULL
  • ship_zipcode — VARCHAR(10) — NOT NULL
  • ship_address — VARCHAR(500) — NOT NULL
  • ship_message — VARCHAR(255)
  • ordered_at — DATETIME — NOT NULL

payment

  • payment_id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.order_id — NOT NULL
  • method — ENUM('CARD', 'TRANSFER', 'EASY_PAY') — NOT NULL
  • amount — INT — NOT NULL
  • status — ENUM('PENDING', 'PAID', 'FAILED', 'CANCELED', 'REFUNDED') — NOT NULL
  • pg_tid — VARCHAR(100) — PG 거래번호(중복 콜백 멱등성 보장용)
  • payment_due_at — DATETIME — 입금기한 = 주문+24h (무통장입금만 사용, 그 외 NULL)
  • paid_at — DATETIME

point_history

  • point_id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → member.member_id — NOT NULL
  • order_id — BIGINT — Foreign key → orders.order_id
  • type — ENUM('EARN', 'USE', 'EXPIRE') — NOT NULL
  • amount — INT — NOT NULL
  • balance — INT — NOT NULL — 잔액
  • created_at — DATETIME — NOT NULL

product

  • product_id — BIGINT — Primary key — NOT NULL
  • product_code — VARCHAR(50) — NOT NULL — 자동생성 상품코드
  • name — VARCHAR(255) — NOT NULL
  • category_id — BIGINT — Foreign key → category.category_id — NOT NULL
  • supplier_id — BIGINT — Foreign key → supplier.supplier_id — NOT NULL
  • price — INT — NOT NULL — 판매가(0 이상)
  • sale_status — ENUM('ON_SALE', 'SOLD_OUT', 'OFF_SALE') — NOT NULL
  • storage_type — ENUM('ROOM', 'COLD', 'FROZEN') — NOT NULL — 실온/냉장/냉동
  • min_shelf_life_days — INT — NOT NULL — 판매 최소 잔여 유통기한 N
  • description — TEXT
  • deleted_at — DATETIME — 소프트딜리트
  • created_at — DATETIME — NOT NULL

product_image

  • image_id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → product.product_id — NOT NULL
  • url — VARCHAR(500) — NOT NULL
  • sort_order — INT — NOT NULL
  • is_main — TINYINT(1) — NOT NULL

qna

  • qna_id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → product.product_id — NOT NULL
  • member_id — BIGINT — Foreign key → member.member_id — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • question — TEXT — NOT NULL
  • answer — TEXT
  • answered_by — BIGINT — Foreign key → admin.admin_id — admin
  • is_public — TINYINT(1) — NOT NULL
  • status — ENUM('WAITING', 'ANSWERED') — NOT NULL
  • deleted_at — DATETIME
  • created_at — DATETIME — NOT NULL

refund

  • refund_id — BIGINT — Primary key — NOT NULL
  • claim_id — BIGINT — Foreign key → claim.claim_id — NOT NULL
  • payment_id — BIGINT — Foreign key → payment.payment_id — NOT NULL
  • amount — INT — NOT NULL — 환불액
  • shipping_deduction — INT — NOT NULL — 단순변심 배송비 차감
  • status — ENUM('PENDING', 'DONE') — NOT NULL
  • refunded_at — DATETIME

review

  • review_id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → product.product_id — NOT NULL
  • member_id — BIGINT — Foreign key → member.member_id — NOT NULL
  • order_item_id — BIGINT — Foreign key → order_item.order_item_id — NOT NULL
  • rating — TINYINT — NOT NULL — 1~5
  • title — VARCHAR(255)
  • content — TEXT — NOT NULL
  • is_public — TINYINT(1) — NOT NULL
  • deleted_at — DATETIME
  • created_at — DATETIME — NOT NULL

shipment

  • shipment_id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.order_id — NOT NULL
  • claim_id — BIGINT — Foreign key → claim.claim_id — 반품/교환 회수·재배송 시 연결
  • direction — ENUM('OUTBOUND', 'INBOUND') — NOT NULL — OUTBOUND=출고/재배송(창고→고객), INBOUND=회수(고객→창고)
  • carrier — VARCHAR(50)
  • tracking_no — VARCHAR(50) — 송장번호
  • status — ENUM('PREPARING', 'SHIPPING', 'DELIVERED') — NOT NULL — INBOUND는 회수준비/회수중/회수완료로 해석
  • shipped_at — DATETIME
  • delivered_at — DATETIME

stock_allocation

  • allocation_id — BIGINT — Primary key — NOT NULL
  • order_item_id — BIGINT — Foreign key → order_item.order_item_id — NOT NULL
  • lot_id — BIGINT — Foreign key → stock_lot.lot_id — NOT NULL
  • qty — INT — NOT NULL — FEFO 차감 수량
  • allocated_at — DATETIME — NOT NULL

stock_disposal

  • disposal_id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → product.product_id — NOT NULL
  • lot_id — BIGINT — Foreign key → stock_lot.lot_id
  • admin_id — BIGINT — Foreign key → admin.admin_id — NOT NULL
  • qty — INT — NOT NULL — 폐기수량
  • reason — ENUM('EXPIRED', 'DAMAGED', 'RETURNED') — NOT NULL — RETURNED=반품/교환 회수품 폐기(신선식품 기본)
  • created_at — DATETIME — NOT NULL

stock_lot

  • lot_id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → product.product_id — NOT NULL
  • supplier_id — BIGINT — Foreign key → supplier.supplier_id — NOT NULL
  • received_date — DATE — NOT NULL — 입고일
  • expiry_date — DATE — NOT NULL — 유통기한
  • initial_qty — INT — NOT NULL — 입고수량
  • available_qty — INT — NOT NULL — 가용재고
  • status — ENUM('AVAILABLE', 'SOLD_OUT', 'DISPOSED') — NOT NULL
  • created_at — DATETIME — NOT NULL

supplier

  • supplier_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL — 공급처
  • contact — VARCHAR(100)