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

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

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