Set up a MySQL database for the game

[Kavignon]

As I am currently still in the design phase, these are some of the ideas I have for the models in the MVP of the game.

Table: Characters

• Description: Stores information about game characters.
• Columns:
  • character_id (Primary Key, Auto Increment)
  • name (String, 255) - The character's name.
  • level (Integer) - The character's level.
  • health (Integer) - The character's health points.
  • experience (Integer) - The character's experience points.
  • class_id (Foreign Key) - The ID of the character's class.
  • user_id (Foreign Key) - The ID of the user who owns the character.

Table: Classes

• Description: Stores information about character classes.
• Columns:
  • class_id (Primary Key, Auto Increment)
  • name (String, 255) - The name of the character class.

Table: Users

• Description: Stores information about game users.
• Columns:
  • user_id (Primary Key, Auto Increment)
  • username (String, 255) - The username of the user.
  • email (String, 255) - The email address of the user.
  • password (String, 255) - The hashed password of the user.
  • registration_date (Date) - The date when the user registered.

Table: InventoryItems

• Description: Stores information about items in a player's inventory.
• Columns:
  • item_id (Primary Key, Auto Increment)
  • character_id (Foreign Key) - The ID of the character who owns the item.
  • item_name (String, 255) - The name of the item.
  • quantity (Integer) - The quantity of the item in the inventory.

Table: Equipment

• Description: Stores information about equipment items.
• Columns:
  • equipment_id (Primary Key, Auto Increment)
  • character_id (Foreign Key) - The ID of the character who equips the item.
  • item_name (String, 255) - The name of the equipment item.
  • slot (String, 50) - The slot where the equipment item is equipped (e.g., "Helmet", "Armor").

Table: Items

• Description: Stores information about game items.
• Columns:
  • item_id (Primary Key, Auto Increment)
  • name (String, 255) - The name of the item.
  • type (String, 50) - The type of the item (e.g., "Weapon", "Consumable").
  • description (Text) - A description of the item.

Table: ItemClasses

• Description: Stores information about item classes.
• Columns:
  • class_id (Primary Key, Auto Increment)
  • name (String, 255) - The name of the item class.

Table: CharacterClasses

• Description: Defines the available character classes.
• Columns:
  • class_id (Primary Key, Auto Increment)
  • name (String, 255) - The name of the character class.

Table: ItemToClassMapping

• Description: Maps items to their allowed character classes.
• Columns:
  • mapping_id (Primary Key, Auto Increment)
  • item_id (Foreign Key) - The ID of the item.
  • class_id (Foreign Key) - The ID of the character class allowed to use the item.

Table: CharacterToClassMapping

• Description: Maps characters to their selected character classes.
• Columns:
  • mapping_id (Primary Key, Auto Increment)
  • character_id (Foreign Key) - The ID of the character.
  • class_id (Foreign Key) - The ID of the character class.

Table: Achievements

• Description: Stores information about in-game achievements.
• Columns:
  • achievement_id (Primary Key, Auto Increment)
  • name (String, 255) - The name of the achievement.
  • description (Text) - A description of the achievement.
  • reward_points (Integer) - The reward points granted upon achieving this.

Table: CharacterAchievements

• Description: Records which achievements each character has completed.
• Columns:
  • record_id (Primary Key, Auto Increment)
  • character_id (Foreign Key) - The ID of the character.
  • achievement_id (Foreign Key) - The ID of the achieved achievement.
  • completion_date (Date) - The date when the achievement was completed.

Table: StoreItems

• Description: Stores information about items available in the in-game store.
• Columns:
  • store_item_id (Primary Key, Auto Increment)
  • item_id (Foreign Key) - The ID of the item.
  • price (Decimal) - The price of the item in the store.