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