umc11 gpt

Tables (12)

Member

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • gender — ENUM('MALE', 'FEMALE', 'NONE') — NOT NULL
  • birth — DATE — NOT NULL
  • address — VARCHAR(100) — NOT NULL — 회원이 입력한 지역/주소; Store.region_id와는 별도
  • detail_address — VARCHAR(255) — NOT NULL
  • phone_number — VARCHAR(20)
  • email — VARCHAR(255) — NOT NULL
  • deleted_at — DATETIME
  • updated_at — DATETIME — NOT NULL
  • created_at — DATETIME — NOT NULL
  • point — INT — NOT NULL — 현재 잔액; Member_Point 내역과 트랜잭션으로 동기화
  • status — ENUM('ACTIVE', 'INACTIVE', 'WITHDRAWAL') — NOT NULL
  • provider — VARCHAR(20) — NOT NULL — 예: GOOGLE
  • provider_subject — VARCHAR(255) — NOT NULL — 소셜 제공자의 고유 ID (예: sub), 이메일 아님

Member_Mission

  • id — BIGINT — Primary key — NOT NULL
  • completed_at — DATETIME
  • status — ENUM('IN_PROGRESS', 'REQUESTED', 'COMPLETED', 'CANCELED') — NOT NULL
  • created_at — DATETIME — NOT NULL — 미션 도전 시작 시각
  • updated_at — DATETIME — NOT NULL
  • member_id — BIGINT — Foreign key → Member.id — NOT NULL
  • mission_id — BIGINT — NOT NULL
  • requested_at — DATETIME — 성공 요청 시각

Member_Point

  • id — BIGINT — Primary key — NOT NULL
  • amount — INT — NOT NULL — 적립은 양수, 사용/전환은 음수
  • member_id — BIGINT — Foreign key → Member.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • member_mission_id — BIGINT — 개별 미션 보상이 아니면 NULL: 10개 달성 보너스 등
  • type — ENUM('MISSION_REWARD', 'ACHIEVEMENT_BONUS', 'CONVERSION', 'ADJUSTMENT') — NOT NULL

Member_food

  • id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → Member.id — NOT NULL
  • food_id — BIGINT — NOT NULL

Mission

  • id — BIGINT — Primary key — NOT NULL
  • content — VARCHAR(500) — NOT NULL — 별도 미션명 없이 화면에 표시되는 내용
  • reward_point — INT — NOT NULL — 고정 포인트 지급 방식을 가정; 5% 지급이면 보상 정책 추가 설계
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • store_id — BIGINT — Foreign key → Store.id — NOT NULL
  • ends_at — DATETIME — NOT NULL

Region

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL

Review

  • id — BIGINT — Primary key — NOT NULL
  • rating — TINYINT — NOT NULL — 리뷰 별점 1~5; 가게 평균 별점은 집계값
  • content — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • member_id — BIGINT — Foreign key → Member.id — NOT NULL
  • store_id — BIGINT — NOT NULL

Review_Picture

  • id — BIGINT — Primary key — NOT NULL
  • picture_url — VARCHAR(2048) — NOT NULL
  • review_id — BIGINT — Foreign key → Review.id — NOT NULL
  • created_at — DATETIME — NOT NULL

Store

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(150) — NOT NULL
  • address — VARCHAR(255) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • region_id — BIGINT — Foreign key → Region.id — NOT NULL
  • food_id — BIGINT — NOT NULL — 가게당 대표 음식 카테고리 1개를 가정

food

  • id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL

member_term

  • member_term_id — BIGINT — Primary key — NOT NULL
  • term_id — BIGINT — Foreign key → term.term_id — NOT NULL
  • member_id — BIGINT — NOT NULL
  • is_agreed — BOOLEAN — NOT NULL — 필수 약관은 TRUE여야 가입 허용; 서비스에서 검증
  • agreed_at — DATETIME — 동의한 경우 동의 시각

term

  • term_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(150) — NOT NULL
  • version — VARCHAR(30) — NOT NULL
  • is_required — BOOLEAN — NOT NULL