DalToori DB

Tables (13)

ai_feedback

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • session_id — BIGINT
  • line_id — BIGINT
  • category — ENUM('tone','speed','pron','rhythm','overall') — NOT NULL
  • score — TINYINT — NOT NULL
  • comment — TEXT — NOT NULL — EXT NOT NUL
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_ai_fb_user_date
  • INDEX — idx_ai_fb_session
  • INDEX — idx_ai_fb_line
  • INDEX — idx_ai_fb_category

game_modes

  • id — BIGINT
  • title — VARCHAR(100) — NOT NULL
  • description — TEXT
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL

game_questions

  • id — BIGINT
  • region_id — BIGINT — NOT NULL
  • question_text — TEXT — NOT NULL
  • option_1 — VARCHAR(255) — NOT NULL
  • option_2 — VARCHAR(255) — NOT NULL
  • option_3 — VARCHAR(255) — NOT NULL
  • option_4 — VARCHAR(255) — NOT NULL
  • correct_option — TINYINT — NOT NULL
  • explanation — TEXT
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_game_questions_region

game_regions

  • id — BIGINT
  • game_mode_id — BIGINT — NOT NULL
  • region_name — ENUM('경상도','전라도','강원도','충청도','제주도') — NOT NULL
  • description — TEXT
  • created_at — DATETIME — NOT NULL

game_results

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • game_mode_id — BIGINT — NOT NULL
  • region_id — BIGINT
  • total_questions — INT — NOT NULL
  • correct_count — INT — NOT NULL
  • score — INT — NOT NULL
  • started_at — DATETIME — NOT NULL
  • ended_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_game_results_user_date
  • INDEX — idx_game_results_mode
  • INDEX — idx_game_results_region

line_scores

  • id — BIGINT
  • session_id — BIGINT — NOT NULL
  • line_id — BIGINT — NOT NULL
  • total_score — TINYINT — NOT NULL
  • tone_score — TINYINT — NOT NULL
  • speed_score — TINYINT — NOT NULL
  • pron_score — TINYINT — NOT NULL
  • rhythm_score — TINYINT — NOT NULL
  • feedback_text — TEXT
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_line_scores_line

practice_sessions

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • script_id — BIGINT — NOT NULL
  • dialect — ENUM('seoul','gyeongsang') — NOT NULL
  • unit_type — ENUM('sentence','paragraph') — NOT NULL
  • started_at — DATETIME — NOT NULL
  • ended_at — DATETIME
  • total_items — INT — NOT NULL
  • completed_items — INT — NOT NULL
  • device_info — VARCHAR(100)
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_sessions_user
  • INDEX — idx_sessions_script

recordings

  • id — BIGINT
  • session_id — BIGINT — NOT NULL
  • line_id — BIGINT — NOT NULL
  • file_url — VARCHAR(255) — NOT NULL
  • mime_type — VARCHAR(50) — NOT NULL
  • sample_rate — INT
  • duration_ms — INT
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_recordings_line

scores

  • id — BIGINT
  • session_id — BIGINT — NOT NULL
  • mode_dialect — ENUM('seoul','gyeongsang') — NOT NULL
  • total_score — TINYINT — NOT NULL
  • tone_score — TINYINT — NOT NULL
  • speed_score — TINYINT — NOT NULL
  • pron_score — TINYINT — NOT NULL
  • rhythm_score — TINYINT — NOT NULL
  • feedback_text — TEXT — NOT NULL
  • extras_json — JSON
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_scores_mode

script_lines

  • id — BIGINT
  • script_id — BIGINT — NOT NULL
  • seq — INT — NOT NULL
  • unit_type — ENUM('sentence','paragraph') — NOT NULL
  • text_standard — TEXT — NOT NULL
  • text_dialect — TEXT
  • audio_standard_url — VARCHAR(255)
  • audio_dialect_url — VARCHAR(255)
  • duration_ms — INT
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_script_lines_unit

scripts

  • id — BIGINT
  • title — VARCHAR(100) — NOT NULL
  • dialect — ENUM('seoul','gyeongsang') — NOT NULL
  • category — ENUM('전달','상황','교양','공공','지식','일상회화') — NOT NULL
  • is_published — TINYINT(1) — NOT NULL
  • order_no — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_scripts_lookup

user_stats

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • total_sessions — INT — NOT NULL
  • total_scripts — INT — NOT NULL
  • recent_study_date — DATE
  • weekly_stats_json — JSON
  • updated_at — DATETIME — NOT NULL

users

  • id — BIGINT
  • login_id — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50)
  • nickname — VARCHAR(50) — NOT NULL
  • profile_image_url — VARCHAR(255)
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • last_login_at — DATETIME
  • INDEX — idx_users_nickname