Replies: 8 comments 3 replies
-
|
A few thoughts as I'm been poking around the database. For all existing pokemon, create a species row to match. Those marked "is_default" should retain the existing link, all others create their own row. Below are some of the manual parsing through for data. For battle forms, such as gmax and mega, these will still have their own pokemon row, however we will repurpose the is_default to "is_base_form" and introduce a new column for "base_pokemon_id" as an optional foreign key to point to the base forms. For instance, Charizard-Gmax would have a base_pokemon_id of 6 to point back to charizard. Charizard itself would have null. I think we could even just take out the is_base_form/is_default entirely in favor of just checking on if that value is null. The api can wrap that logic into an implicit field if need be, but I think it should be clear to most people. These forms change the pokemon, so they would need their own data rows for things like mvoes, abilities, types etc. For less modifying forms, such as cosemetic forms, we will still create a pokemon row, but handle things a bit different as explained later Statement to identify duplicate species SELECT
ps.id AS species_id,
ps.name AS species_name,
COUNT(p.id) AS pokemon_count,
GROUP_CONCAT(p.name, ', ') AS pokemon_names
FROM pokemon_v2_pokemon AS p
JOIN pokemon_v2_pokemonspecies AS ps
ON ps.id = p.pokemon_species_id
GROUP BY ps.id, ps.name
HAVING COUNT(p.id) > 1
ORDER BY pokemon_count DESC, ps.name;Spcies with multiple pokemon The previous results include all alternates, if we want to be more specific for data that is not including battle forms, we can do the following SELECT
ps.id AS species_id,
ps.name AS species_name,
COUNT(p.id) AS pokemon_count,
GROUP_CONCAT(p.name, ', ') AS pokemon_names
FROM pokemon_v2_pokemon AS p
JOIN pokemon_v2_pokemonspecies AS ps
ON ps.id = p.pokemon_species_id
WHERE p.name NOT LIKE '%-mega%'
AND p.name NOT LIKE '%-gmax%'
GROUP BY ps.id, ps.name
HAVING COUNT(p.id) > 1
ORDER BY pokemon_count DESC, ps.name;Which will produce With this list we will need to heavily modify the pokemon/species data in order to flatten this data. I believe the best option is to migrate in place, I will create a new species for all duplicate pokemon with the same name as their species as the pokemon name. This will break all relationships where not the base form, however the optional reference to the base pokemon will allow that information to be built back in. Once this is done, I will go through and generate the data for the csv import and modify the db build to have a single pokemon table with all this data, updating all foreign keys that targetted species and pointing them to the pokemon_id. In my logic I will have to ensure that where there was a fk to species, it is updated to the pokemon_id, since those are not perfectly aligned. Existing refernces to pokemon_id as a fk should be fine, as we are basing off of that for the new ids |
Beta Was this translation helpful? Give feedback.
-
|
Adding a reference for all alternate forms https://bulbapedia.bulbagarden.net/w/index.php?title=List_of_Pok%C3%A9mon_with_form_differences https://bulbapedia.bulbagarden.net/w/index.php?title=Mega_Evolution |
Beta Was this translation helpful? Give feedback.
-
|
Please take a look here as well |
Beta Was this translation helpful? Give feedback.
-
I had not see this thread before, it seems this is something already in discussion. Not surprised, as it seems clear to me we're all looking at the same issues. I'll read through that discussion, and discuss in that. Thanks for the link |
Beta Was this translation helpful? Give feedback.
-
|
Would doing this in v2 help prepare? It would be adding This is just a few of the 17 pikachu entries: {
"id": 25,
"name": "pikachu",
"is_default": true,
"base_pokemon": null,
"form_type": "base"
}
{
"id": 10058,
"name": "pikachu-rock-star",
"is_default": false,
"base_pokemon": 25,
"form_type": "cosmetic"
}
{
"id": 10059,
"name": "pikachu-belle",
"is_default": false,
"base_pokemon": 25,
"form_type": "cosmetic"
}
{
"id": 10158,
"name": "pikachu-gmax",
"is_default": false,
"base_pokemon": 25,
"form_type": "battle"
} |
Beta Was this translation helpful? Give feedback.
-
|
I think the overall goal with v2 is to keep it somewhat as is since a lot of people are using it, so might be best for v3. This is kinda the idea, but we'd want to normalize the form types into their own table with an id reference. This allows us to create and define forms, as well as wire up translations and descriptors texts and not be bound to the Pokémon itself. There are likely going to be Pokémon specific form entries on that table, but that's fine |
Beta Was this translation helpful? Give feedback.
-
|
Makes sense, we should flatten the model so that Species becomes Pokemon, and all alternate forms (gmax, regional, costumes) become entries of that same table. We can add a base_pokemon_id field and a form_type (base, cosmetic, regional or battle). |
Beta Was this translation helpful? Give feedback.
-
|
Yes flatten it ideally with a script, so that we can pull in new contributions for the actual v2 while being able to re-generate v3 data.Or is it something useless? |
Beta Was this translation helpful? Give feedback.
-
No because we run the risk of braking thousands of people's applications by changing the data. We should rarely add new attributes and never modify existing ones. A V3 is the right way to go with any major refactoring. |
Beta Was this translation helpful? Give feedback.
-
|
Is there already a repo for v3? |
Beta Was this translation helpful? Give feedback.
-
|
Nope, we can use this one |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
-
Currently, Pokemon, Species, Form etc are not structured in a way to handle more modern games and new forms. There are also some shortcomings in identifying game specific regions and dex numbers.
I plan to create a PR to address these challenges and structure the data in a way that is more flexible to regionally, game specific forms. This will mainly revolve around flattening the data to the Species table becoming the Pokemon table, and Pokemon being repurposed for specific forms that are not different Pokemon (specific data required should be discussed, but I'm thinking forms that don't change base stats, types, etc would be considered the same Pokemon).
If anyone has input please let me know, I'll share schema changes as I go, I'm no pokemon expert, especially with the recent games so if there are specific form considerations etc please share.
Beta Was this translation helpful? Give feedback.
All reactions