db설계v1

테이블 (9)

favorites

  • id — BIGINT
  • user_id — BIGINT — 외래 키 → users.id — NOT NULL
  • word_id — BIGINT — 외래 키 → words.id — NOT NULL
  • created_at — DATETIME — NOT NULL

test_results

  • id — BIGINT
  • test_session_id — BIGINT — 외래 키 → test_sessions.id — NOT NULL
  • word_id — BIGINT — 외래 키 → words.id — NOT NULL
  • question_type — ENUM('KOR_TO_ENG', 'ENG_TO_KOR') — NOT NULL
  • user_answer — VARCHAR(255)
  • correct_answer — VARCHAR(255) — NOT NULL
  • is_correct — TINYINT(1) — NOT NULL
  • answered_at — DATETIME — NOT NULL

test_sessions

  • id — BIGINT
  • user_id — BIGINT — 외래 키 → users.id — NOT NULL
  • word_book_id — BIGINT — 외래 키 → word_books.id
  • test_type — ENUM('KOR_TO_ENG', 'ENG_TO_KOR', 'RANDOM') — NOT NULL
  • total_count — INT — NOT NULL
  • correct_count — INT — NOT NULL
  • started_at — DATETIME — NOT NULL
  • finished_at — DATETIME

user_game_profiles

  • id — BIGINT
  • user_id — BIGINT — 외래 키 → users.id — NOT NULL
  • level — INT — NOT NULL
  • exp — INT — NOT NULL
  • coin — INT — NOT NULL
  • character_stage — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

users

  • id — BIGINT
  • email — VARCHAR(100) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • nickname — VARCHAR(50) — NOT NULL
  • role — ENUM('USER', 'ADMIN') — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

word_book_words

  • id — BIGINT
  • word_book_id — BIGINT — 외래 키 → word_books.id — NOT NULL
  • word_id — BIGINT — 외래 키 → words.id — NOT NULL
  • added_at — DATETIME — NOT NULL

word_books

  • id — BIGINT
  • owner_user_id — BIGINT — 외래 키 → users.id
  • title — VARCHAR(100) — NOT NULL
  • description — VARCHAR(255)
  • book_type — ENUM('SYSTEM', 'CUSTOM') — NOT NULL
  • is_public — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

words

  • id — BIGINT
  • english_word — VARCHAR(100) — NOT NULL
  • meaning — VARCHAR(255) — NOT NULL
  • part_of_speech — ENUM( 'noun', 'verb', 'adjective', 'adverb', 'preposition', 'conjunction', 'pronoun', 'phrase' ) — NOT NULL
  • example_sentence — TEXT
  • pronunciation_url — VARCHAR(255)
  • source_type — ENUM('SYSTEM', 'USER') — NOT NULL
  • created_by_user_id — BIGINT — 외래 키 → users.id
  • is_deleted — TINYINT(1) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

wrong_answers

  • id — BIGINT
  • user_id — BIGINT — 외래 키 → users.id — NOT NULL
  • word_id — BIGINT — 외래 키 → words.id — NOT NULL
  • wrong_count — INT — NOT NULL
  • last_wrong_at — DATETIME — NOT NULL
  • last_reviewed_at — DATETIME
  • is_resolved — TINYINT(1) — NOT NULL