MMORPG DB설계

테이블 (17)

bossrecords

  • boss_id — INT — 기본 키 — NOT NULL
  • character_id — INT — 외래 키 → characters.character_id — NOT NULL
  • cleared_at — DATETIME
  • clear_time_seconds — INT

characterequipment

  • character_id — INT — 외래 키 → characters.character_id — NOT NULL
  • item_id — INT — 외래 키 → items.item_id — NOT NULL
  • equip_slot — VARCHAR(255)

characters

  • character_id — INT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id
  • character_name — VARCHAR(255) — NOT NULL
  • level — INT
  • job — VARCHAR(255)
  • appearance_data — TEXT
  • current_map — VARCHAR(255)
  • position_x — FLOAT
  • position_y — FLOAT
  • position_z — FLOAT
  • rotation_y — FLOAT
  • gold — BIGINT
  • created_at — DATETIME

characterstats

  • character_id — INT — 외래 키 → characters.character_id — NOT NULL
  • experience — INT
  • damage — INT
  • speed — INT
  • attack — INT
  • defense — INT
  • currentHp — INT
  • maxHp — INT

comments

  • comment_id — INT — 기본 키 — NOT NULL
  • post_id — INT — 외래 키 → posts.post_id — NOT NULL
  • author_character_id — INT — 외래 키 → characters.character_id
  • author_user_id — BIGINT — 외래 키 → users.user_id
  • content — TEXT — NOT NULL
  • created_at — DATETIME
  • parent_comment_id — INT — 외래 키 → comments.comment_id
  • is_deleted — TINYINT(1)

dropitems

  • drop_item_id — INT — 기본 키 — NOT NULL
  • item_id — INT — 외래 키 → items.item_id
  • drop_rate — FLOAT
  • min_quantity — INT
  • max_quantity — INT

inventory

  • inventory_slot — INT — 기본 키 — NOT NULL
  • character_id — INT — 외래 키 → characters.character_id — NOT NULL
  • item_id — INT — 외래 키 → items.item_id — NOT NULL
  • inventory_type — VARCHAR(255)
  • quantity — INT
  • item_spec — JSON

items

  • item_id — INT — 기본 키 — NOT NULL
  • item_name — VARCHAR(255)
  • item_type — VARCHAR(255)
  • item_description — TEXT
  • max_stack_size — INT
  • is_tradable — BOOLEAN
  • base_stats — TEXT
  • sell_price — INT
  • icon_path — VARCHAR(255)
  • attack_power — INT
  • defense — INT
  • hp_bonus — INT
  • attack_speed_bonus — FLOAT
  • damage_reduction — FLOAT

like_user

  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • post_id — INT — 외래 키 → posts.post_id — NOT NULL

marketlistings

  • listing_id — INT — 기본 키 — NOT NULL
  • seller_character_id — INT — 외래 키 → characters.character_id
  • item_id — INT — 외래 키 → items.item_id
  • quantity — INT
  • price — BIGINT
  • listed_at — DATETIME
  • expires_at — DATETIME
  • item_spec — TEXT

monsters

  • monster_id — INT — 기본 키 — NOT NULL
  • monster_name — VARCHAR(255)
  • level — INT
  • hp — INT
  • attack — INT
  • defense — INT
  • xp_reward — INT
  • gold_reward — INT
  • drop_item_group_id — INT

posts

  • post_id — INT — 기본 키 — NOT NULL
  • board_type — VARCHAR(255)
  • author_user_id — BIGINT — 외래 키 → users.user_id
  • author_character_id — INT — 외래 키 → characters.character_id
  • title — VARCHAR(255)
  • content — TEXT
  • created_at — DATETIME
  • updated_at — DATETIME
  • view_count — INT
  • like_count — INT
  • comments_count — INT
  • is_deleted — BOOLEAN
  • event_start_date — DATETIME
  • event_end_date — DATETIME

questprogress

  • quest_progress_id — INT — 기본 키 — NOT NULL
  • character_id — INT — 외래 키 → characters.character_id — NOT NULL
  • quest_id — INT — 외래 키 → quests.quest_id — NOT NULL
  • current_progress_data — TEXT
  • completed_at — DATETIME

quests

  • quest_id — INT — 기본 키 — NOT NULL
  • quest_name — VARCHAR(255)
  • npc_id — TEXT
  • step_descriptions — TEXT
  • reward_xp — INT
  • reward_gold — INT
  • reward_item_id — INT — 외래 키 → items.item_id
  • reward_item_quantity — INT
  • prerequisite_quest_id — INT
  • minimum_level — INT
  • script — TEXT

rankings

  • ranking_id — INT — 기본 키 — NOT NULL
  • ranking_type — VARCHAR(255)
  • character_id — INT — 외래 키 → characters.character_id
  • rank — INT
  • ranking_value — BIGINT
  • recorded_at — DATETIME

tradehistory

  • trade_id — INT — 기본 키 — NOT NULL
  • buyer_character_id — INT — 외래 키 → characters.character_id
  • seller_character_id — INT — 외래 키 → characters.character_id
  • item_id — INT
  • quantity — INT
  • price — BIGINT
  • traded_at — DATETIME

users

  • user_id — BIGINT — 기본 키 — NOT NULL
  • nickname — VARCHAR(255) — NOT NULL
  • email — VARCHAR(255)
  • profile_image — VARCHAR(255)