૭( ᵕ•̀ᵕ•́૭)

Complete Database Design

5 min read

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───

ERD

Entity Descriptions

POCKETMON

Pocketmon are the centerpiece of the game, they are the characters that the players can use in their adventures along their game. They can have unique moves, stats, and many more things that effect the gameplay.

Columns & Relations

  • id (int) PK
    • The id of the Pocketmon in order of addition
  • base_hp (tinyint)
    • The base health of the Pocketmon before any stat changing moves
  • base_attack (tinyint)
    • The base attack of the Pocketmon before any stat changing moves
  • base_defense (tinyint)
    • The base defense of the Pocketmon before any stat changing moves
  • base_speed (tinyint)
    • The base speed of the Pocketmon before any stat changing moves
  • base_special_attack (tinyint)
    • The base special attack of the Pocketmon before any stat changing moves
  • base_special_defense (tinyint)
    • The base special defense of the Pocketmon before any stat changing moves
  • generation (tinyint)
    • The generation of the game that the Pocketmon was added
  • evolves_from_id (int) FK → POCKETMON.id
    • This is a circular reference that is for Pocketmon that evolve from other Pocketmon; used for evolutionary lines

MOVE

Moves are the “actions” of the Pocketmon. Moves are either physical, special, or status depending on what they do. An example of a physical attack could be something like “tackle”, whereas a special attack would be something like “flamethrower”. Status moves could be something like “toxic”—which inflicts the poison status effect on the opponent.

Columns & Relations

  • id (int) PK
    • The id of the move
  • type_id (int) FK → TYPE.id
    • The id of the type of the moves; moves have types to do more/less damage to certain Pocketmon based on their weaknesses/strengths
  • name (varchar(max))
    • The name of the move, such as “Flamethrower”
  • power (tinyint)
    • The base power of the move, actual damage is calculated based on type strengths, STAB, critical hits, etc.
  • accuracy (decimal(3,2))
    • The accuracy of the move, less accurate moves are more likely to fail. For instance, a 0.50 accuracy would hit 50% of the time
  • pp (tinyint)
    • Short for “power points”, the base number of times this move can be used before replenishing; can be increased with items
  • category (varchar(max))
    • The category of the move, such as physical, special, or status moves

POCKETMON_MOVE

This is a junction/bridge table that connects many Pocketmon to many moves since SQL does not support many-to-many relationships without a junction table. This maps all of the Pocketmon and their respective moves, since moves can have multiple Pocketmon, and Pocketmon can have multiple moves.

Columns & Relations

  • move_id (int) PK, FK → MOVE.id
    • The id of the move that this table connects to
  • pocketmon_id (int) PK, FK → POCKETMON.id
    • The id of the Pocketmon that this table connects to
  • method (varchar(max))
    • The method of learning the move, such as via leveling or with a TM

ABILITY

Abilities are special traits Pocketmon can have. For instance, “levitate” makes the Pocketmon invulnerable to ground attacking moves.

Columns & Relations

  • id (int) PK
    • The id of the ability
  • name (varchar(max))
    • The name of the ability, such as “Swift Swim”
  • effect (varchar(max))
    • The description/effect of the ability, such as Swift Swim being “Boosts the Pocketmon’s speed in the rain”

POCKETMON_ABILITY

This is another junction table that connects abilities to Pocketmon, much like the POCKETMON_MOVE or POCKETMON_TYPE table, since many Pocketmon can have many abilities. For instance, a Pocketmon can have abilities, such as Pulbasaur can have “Overgrow” and “Chlorophyll”. Hidden abilities, which are abilities that are significantly more rare to find on a Pocketmon, are also specified in this table. For instance, there is a hidden ability, “Libero” that makes it where the attacker’s type switches to the most recently used move’s type.

Columns & Relations

  • ability_id (int) PK, FK → ABILITY.id
    • The id of the ability that this table connects to
  • pocketmon_id (int) PK, FK → POCKETMON.id
    • The id of the Pocketmon that this table connects to
  • hidden (bit)
    • Whether the ability is hidden, meaning a more rare ability that is typically not on the Pocketmon.

TYPE

The type of the Pocketmon. Types effect how battling works, since certain types are strong, weak, or invulnerable to other types, specified in the POCKETMON_RELATIONS table.

Columns & Relations

  • id (int) PK
    • The id of the type
  • name (varchar(max))
    • The name of the type, such as “Fire” or “Water”

POCKETMON_TYPE

This is a junction table that connects the types to their Pocketmon. This is very similar to the other junction tables in functionality, just slightly adjusted for their types.

Columns & Relations

  • type_id (int) PK, FK → TYPE.id
    • The id of the type that this table connects to
  • pocketmon_id (int) PK, FK → POCKETMON.id
    • The id of the Pocketmon that this table connects to

POCKETMON_RELATIONS

This table specifies how types should interact with each other. For instance, fire types are weaker to water type moves, whereas water types are resistant against fire type moves.

Columns & Relations

  • attacker_type_id (int) PK, FK → TYPE.id
    • The id of the type that is attacking. Such as fire attacking water.
  • defender_type_id (int) PK, FK → TYPE.id
    • The id of the type that is receiving the attack, such as water being attacked by fire.
  • multiplier (decimal(3,2))
    • The multiplier of the attack. For instance, fire attacking water would be a 0.5 multiplier.

───✱*.｡:｡✱*.:｡✧*.｡✰*.:｡✧*.｡:｡*.｡✱ ───

Graph View