codemate(refactored)

Tables (9)

member

  • id — BIGINT
  • email — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • boj_handle — VARCHAR(50) — BOJ 핸들
  • provider — VARCHAR(50) — NOT NULL — GOOGLE, etc
  • provider_id — VARCHAR(255) — NOT NULL
  • role — VARCHAR(20) — NOT NULL — USER, ADMIN
  • created_at — DATETIME(6) — NOT NULL
  • modified_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)

member_recommendation

  • id — BIGINT
  • member_id — BIGINT — NOT NULL
  • recommendation_id — BIGINT — NOT NULL
  • email_send_status — VARCHAR(20) — NOT NULL — PENDING, SENT, FAILED
  • email_sent_at — DATETIME(6) — 이메일 발송 성공 시각
  • created_at — DATETIME(6) — NOT NULL
  • modified_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)

member_recommendation_problem

  • id — BIGINT
  • member_id — BIGINT — NOT NULL
  • member_recommendation_id — BIGINT — NOT NULL
  • problem_id — BIGINT — NOT NULL — denormalized - 성능 최적화
  • team_id — BIGINT — denormalized - DB FK 없음, 팀 삭제 후에도 보존
  • team_name — VARCHAR(255) — denormalized - 팀 삭제 후에도 표시 가능
  • recommendation_date — DATE — NOT NULL — denormalized - 조회 성능 최적화
  • created_at — DATETIME(6) — NOT NULL
  • modified_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)

member_solved_problem

  • id — BIGINT
  • member_id — BIGINT — NOT NULL
  • problem_id — BIGINT — NOT NULL
  • solved_at — DATETIME(6) — NOT NULL — 문제 해결 시각
  • created_at — DATETIME(6) — NOT NULL
  • modified_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)

problem

  • id — BIGINT
  • problem_id — INTEGER — NOT NULL — BOJ 문제 번호
  • title — VARCHAR(255) — NOT NULL
  • tier — INTEGER — solved.ac 티어
  • tags — TEXT — JSON 배열 형태의 태그
  • url — VARCHAR(500)
  • created_at — DATETIME(6) — NOT NULL
  • modified_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)

recommendation

  • id — BIGINT
  • recommendation_date — DATE — NOT NULL
  • type — VARCHAR(20) — NOT NULL — SCHEDULED, MANUAL
  • team_id — BIGINT — 애플리케이션 FK (DB 제약조건 없음), 팀 삭제 후에도 데이터 보존
  • created_at — DATETIME(6) — NOT NULL
  • modified_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6) — soft delete

recommendation_problem

  • id — BIGINT
  • recommendation_id — BIGINT — NOT NULL
  • problem_id — BIGINT — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • modified_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)

team

  • id — BIGINT
  • name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • difficulty_preset — VARCHAR(50) — 난이도 프리셋
  • recommendation_days — VARCHAR(50) — 추천 요일 (예: MONDAY,WEDNESDAY)
  • recommendation_time — TIME — 추천 시간
  • recommendation_status — VARCHAR(20) — ACTIVE, INACTIVE
  • created_at — DATETIME(6) — NOT NULL
  • modified_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)

team_member

  • id — BIGINT
  • team_id — BIGINT — NOT NULL
  • member_id — BIGINT — NOT NULL
  • role — VARCHAR(20) — NOT NULL — LEADER, MEMBER
  • created_at — DATETIME(6) — NOT NULL
  • modified_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)