2-project

Tables (18)

game_genres

  • game_id — BIGINT — Foreign key → games.id — NOT NULL
  • genre_id — BIGINT — Foreign key → genres.id — NOT NULL

game_platforms

  • game_id — BIGINT — Foreign key → games.id — NOT NULL
  • platform_id — BIGINT — Foreign key → platforms.id — NOT NULL

game_series

  • id — BIGINT — Primary key — NOT NULL
  • igdb_id — BIGINT
  • name — VARCHAR(255) — NOT NULL

game_series_games

  • series_id — BIGINT — Foreign key → game_series.id — NOT NULL
  • game_id — BIGINT — Foreign key → games.id — NOT NULL

games

  • id — BIGINT — Primary key — NOT NULL
  • igdb_id — BIGINT
  • title — VARCHAR(255) — NOT NULL
  • cover_image_url — VARCHAR(500)
  • developer — VARCHAR(255)
  • release_date — DATE
  • description — TEXT
  • igdb_rating — DECIMAL(5,2)
  • avg_rating — DECIMAL(2,1)
  • review_count — INT
  • like_count — INT
  • played_count — INT
  • playing_count — INT
  • backlog_count — INT
  • wishlist_count — INT
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

genres

  • id — BIGINT — Primary key — NOT NULL
  • igdb_genre_id — BIGINT
  • name — VARCHAR(50) — NOT NULL

platforms

  • id — BIGINT — Primary key — NOT NULL
  • igdb_platforms_id — BIGINT
  • name — VARCHAR(50) — NOT NULL

review_comment_likes

  • comment_id — BIGINT — Foreign key → review_comments.id — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL

review_comments

  • id — BIGINT — Primary key — NOT NULL
  • review_id — BIGINT — Foreign key → reviews.id — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • content — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

review_likes

  • review_id — BIGINT — Foreign key → reviews.id — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL

review_reports

  • id — BIGINT — Primary key — NOT NULL
  • review_id — BIGINT — Foreign key → reviews.id — NOT NULL
  • reporter_id — BIGINT — Foreign key → users.id — NOT NULL
  • reason — VARCHAR(255) — NOT NULL
  • status — ENUM('PENDING', 'APPROVED', 'REJECTED')
  • created_at — DATETIME — NOT NULL

reviews

  • id — BIGINT — Primary key — NOT NULL
  • user_game_id — BIGINT — Foreign key → user_games.id — NOT NULL
  • rating — DECIMAL(2,1) — NOT NULL
  • content — TEXT
  • is_spoiler — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_favorite_games

  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • game_id — BIGINT — Foreign key → games.id — NOT NULL
  • display_order — INT — NOT NULL

user_follows

  • follower_id — BIGINT — Foreign key → users.id — NOT NULL
  • following_id — BIGINT — Foreign key → users.id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_games

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • game_id — BIGINT — Foreign key → games.id — NOT NULL
  • play_status — ENUM( 'PLAYED', 'COMPLETED', 'RETIRED', 'SHELVED', 'ABANDONED' )
  • is_playing — BOOLEAN — NOT NULL
  • is_backlog — BOOLEAN — NOT NULL
  • is_wishlist — BOOLEAN — NOT NULL
  • is_liked — BOOLEAN — NOT NULL
  • platform_id — BIGINT — Foreign key → game_platforms.platform_id
  • play_time_hours — DECIMAL(8,2)
  • finish_time_hours — DECIMAL(8,2)
  • master_time_hours — DECIMAL(8,2)
  • started_at — DATE
  • completed_at — DATE
  • last_played_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_preference_games

  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • game_id — BIGINT — Foreign key → games.id — NOT NULL

user_preference_genres

  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • genre_id — BIGINT — Foreign key → genres.id — NOT NULL

users

  • id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • nickname — VARCHAR(50) — NOT NULL
  • profile_image_url — VARCHAR(500)
  • bio — VARCHAR(200)
  • onboarding_completed — BOOLEAN — NOT NULL
  • role — VARCHAR(20)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL