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)