speakcoding

Tables (10)

User

  • user_id (id) — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • nickname — VARCHAR(50) — NOT NULL
  • current_level — INT
  • experience_points — INT
  • created_at — TIMESTAMP
  • updated_at
  • level — INT

attendance

  • attendance_id (id) — Primary key — NOT NULL
  • user_id — Foreign key → User.user_id — NOT NULL
  • attendanceDate (attendance_date) — LocalDate
  • checkedAt (checked_at) — LocalDateTime
  • streak_count (streakCount) — INT

leagues

  • league_id — INT
  • season — VARCHAR(50) — NOT NULL
  • league_name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • status — ENUM('upcoming', 'active', 'completed')
  • max_participants — INT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

payments

  • payment_id — INT
  • user_id — INT — NOT NULL
  • amount — DECIMAL(10,2) — NOT NULL
  • currency — VARCHAR(3)
  • payment_method — VARCHAR(50)
  • status — ENUM('pending', 'completed', 'failed', 'refunded', 'partially_refunded')
  • external_payment_id — VARCHAR(100)
  • payment_gateway — VARCHAR(50)
  • refund_amount — DECIMAL(10,2)
  • refund_reason — TEXT
  • refund_processed_at — TIMESTAMP
  • billing_period_start — DATE
  • billing_period_end — DATE
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

problem_evaluations

  • id (evaluation_id) — INT — Primary key
  • user_id (id2) — Foreign key → User.user_id — NOT NULL
  • problem_id — INT — Foreign key → problems.problem_id
  • rating — INT
  • feedback — TEXT
  • is_reported — BOOLEAN
  • report_reason — TEXT
  • created_at — TIMESTAMP

problems

  • problem_id — INT — Primary key
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT — NOT NULL
  • level — INT — NOT NULL
  • category — VARCHAR(50) — NOT NULL
  • problem_type — ENUM('block_coding', 'fill_blank', 'code_editor', 'level_test') — NOT NULL
  • difficulty — ENUM('beginner', 'intermediate', 'advanced')
  • problem_content — TEXT
  • solution — TEXT
  • test_cases — TEXT
  • hints — TEXT
  • rating — DECIMAL(3,2)
  • rating_count — INT
  • is_active — BOOLEAN
  • is_leveltest — BOOLEAN
  • estimated_time_minutes — INT
  • tags — TEXT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

statistics

  • stat_id — INT — Primary key — NOT NULL
  • user_id (id2) — INT — Foreign key → User.user_id — NOT NULL
  • period_start — DATE — NOT NULL
  • period_end — DATE — NOT NULL
  • problems_solved — INT
  • problems_attempted — INT
  • correct_submissions — INT
  • total_submissions — INT
  • time_spent_minutes — INT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

submissions

  • submission_id — INT
  • user_id — INT — NOT NULL
  • problem_id — INT — NOT NULL
  • submitted_code — TEXT
  • result — ENUM('correct', 'incorrect', 'error', 'timeout') — NOT NULL
  • score — INT
  • execution_time_ms — INT
  • memory_usage_kb — INT
  • error_message — TEXT
  • submitted_at — TIMESTAMP

surveys

  • survey_id — INT
  • user_id — INT — NOT NULL
  • job_title — VARCHAR(100)
  • learning_purpose — TEXT
  • current_skill_level — INT
  • motivation — TEXT
  • time_availability — VARCHAR(50)
  • preferred_language — VARCHAR(50)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

user_league_participation

  • participation_id — INT
  • user_id — INT — NOT NULL
  • league_id — INT — NOT NULL
  • score — INT
  • rank_position — INT
  • problems_solved — INT
  • time_spent_minutes — INT
  • joined_at — TIMESTAMP
  • last_activity — TIMESTAMP