2
\$\begingroup\$

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?

\$\endgroup\$
7
\$\begingroup\$

Create an intermediary table that maps Pokemon to learnable skills:

enter image description here

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
\$\endgroup\$
  • \$\begingroup\$ Naive question! Why don't you put LearnableID directly inside the Pokemon table? \$\endgroup\$ – Cristol.GdM Apr 19 '14 at 16:56
  • \$\begingroup\$ Placing it in a separate table allows for multiple LearnableSkills IDs. If you place the LearnableID directly in the Pokemon table, you only get one LearnableSkills ID or you're required to do workarounds like using a comma separated list that needs to be parsed. \$\endgroup\$ – MichaelHouse Apr 19 '14 at 16:59
  • \$\begingroup\$ Ok I see. So if each Pokemon has only one Learnable skillset, would it be good practice to put LearnableID directly inside the Pokemon table? \$\endgroup\$ – Cristol.GdM Apr 19 '14 at 18:18
  • 1
    \$\begingroup\$ Yes. That would work, if you have skillsets, this question was about learnable skills or moves, not sets. \$\endgroup\$ – MichaelHouse Apr 19 '14 at 18:25
  • 3
    \$\begingroup\$ This is a trivial query. \$\endgroup\$ – MichaelHouse Apr 19 '14 at 19:16
-2
\$\begingroup\$

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
\$\endgroup\$
  • 1
    \$\begingroup\$ This ignores the fact that not every Pokemon can learn every move of its own type. Also Pokemon can learn moves that have different types than themselves and they can have more than one type. \$\endgroup\$ – UnholySheep Dec 30 '16 at 19:37
  • \$\begingroup\$ I agree with the above comment, but at the end of the day, the question isn't asking "How did Pokemon do it", its asking " How should I do it". If you retail or this answer to be an example of how you might do it, instead of an inaccurate representation of how Pokemon does it, I will consider reversing my downvote \$\endgroup\$ – Gnemlock Dec 30 '16 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.