In MySQL, how would I model moves that Pokemon can learn?

Question

I'm making a Pokemon MMO Game with Unity + SmartFoxServer/MySQL. At the moment I'm creating the base structure for the database.

If you are not familiar with Pokemon Games, every Pokemon can learn only a fixed set of moves. I want to represent this in my database. How should I do it?

---

Currently I have a pokemon_base table that is storing all the base data of the Pokemon, like name, elemental types, base stats, etc, and a Unique Identifier. I need for every Pokemon inside this table to have a list of learnable moves.

I have these ideas to continue:

• Have a field on the Pokemon table containing comma-separated IDs of learnable moves
• Have a field on the Moves table containing comma-separated IDs of Pokemon that can learn this move, or
• Have another Table with a Field for Pokemon ID and boolean fields for each move

The first one to me seems the better because once I have a Pokemon ID I can get all the moves in a string, which I can split by commas and do my checks. Have I missed something?

Answer 1

Create an intermediary table that maps Pokemon to learnable skills:

Now you can select all the learnables for a Pokemon by name:

SELECT LearnableSkills.ID FROM LearnableSkills, PokemonLearnables, Pokemon
WHERE LearnableSkills.ID = PokemonLearnables.LearnableID
AND PokemonLearnables.PokemonID = Pokemon.ID
AND Pokemon.Name = 'byte56'

Or by ID:

SELECT LearnableSkills.ID FROM LearnableSkills, PokemonLearnables
WHERE LearnableSkills.ID = PokemonLearnables.LearnableID
AND PokemonLearnables.PokemonID = 56

Answer 2

I don't have time at the moment to create a visio diagram showing this in practice, but in within the Pokemon games the move sets have an elemental type that determines which Pokemon can learn those moves. As such, I don't know if it's best to keep a table listing all of the available moves each Pokemon can learn as answered above.

For instance, ThunderShock is an electric type move that electric type Pokemon can learn.

The elemental types include:

• Water
• Grass
• Fire
• Ice
• Electric
• Psychic
• Dragon
• Dark
• etc. (and more I believe)

Within your database, I'd suggest the following tables:

ElementalType (primary key ID, Name)

Pokemon (primary key ID/#, Name, foreign key ElementalType.id as TypeID)

Moves (primary key ID, Name, Damage, PowerPointsRequired, foreign key ElementalType.id as TypeID)

To store moves known, an association table LearnedMoves (foreign key Pokemon.id as PokemonID, foreign key Moves.ID as MoveID)

Then to show the moves a Pokemon could learn, you could run the following query:

    SELECT * from Moves m
    INNER JOIN ElementalType t ON m.TypeID = t.ID 
    INNER JOIN Pokemon p ON p.TypeID = t.ID
    WHERE p.ID = @PokemonID