Error in the data model regarding attributes of species/pokémon

[ivanlonel]

The current model has some relationships between entities that used to work in earlier generations, but have become inaccurate, at least since the introduction of regional variants (unless you consider Burmy/Shellos/Deerling/Flabébé/Pumpkaboo). Specifically, some attributes that should be linked to specific pokémon (or maybe even each cosmetic form) are still tied to the whole pokémon species.

The first one that comes to mind is evolution chain. For example, Kantonian Meowth, Alolan Meowth and Galarian Meowth each have a separate evolution chain. A-Meowth can't evolve to K-Persian and vice-versa. If I want to get, for example, all moves each evolved pokémon can learn by including their pre-evo moves (#897), there's no way to separate which moves come from each possible base form in a species with more than one evolution chain (other than handling these cases separately). This has been brought up before in #966, #844 and #655, for example.

has_gender_differences should also be an attribute of each individual pokémon. Kantonian Rattata/Raticate have gender differenciation, whereas Alolan Rattata/Raticate don't.

Same goes for pokemon_color_id. Alolan Sandshrew isn't yellow.

There might be more. Those are only the more obvious when taking a glance at table pokemon_v2_pokemonspecies.

[GreatNovaDragon]

I am of the opinion that the species category shouldnt exist in the first place nowadays. and all the data that is in there should get included into the actual pokemon category.

it would probably have been a patchwork thing when forms didnt have pokedex entries, which changed since mega dex and regional variant dex entries in sun and moon anyways.

[ivanlonel]

Agreed. The line between what should be a form or a different pokémon seems kinda blurry anyway. As stated in #563, Magearna forms only differ cosmetically, but it has 2 entries in the pokémon table. Same goes for the 7 Minior core colors. Meanwhile, Arceus/Silvally types are considered just forms, despite having different types and therefore behaving differently in battle.

It would make more sense to have every possible form in a single table. Maybe adding an is_cosmetic_only attribute.

Moreover, I think the primary key for that single table should be composed of two columns: species_id and form_id. That would completely remove the need for the arbitrarily-assigned ids beyond 10000 which are used today. form_id=1 would always be the "default" form, and the other forms would increase form_id sequentially, like this:	species_id	form_id	name	...
52	1	meowth	...
52	2	meowth-alola	...
52	3	meowth-galar	...
53	1	persian	...
53	2	persian-alola	...
54	1	psyduck	...
55	1	golduck	...

[GreatNovaDragon]

Another possible key using, would be using the name field as an id, that's unique anyways.

i would not do an is_cosmetic_only attribute, because by all intents of purposes, for the current model, Gigantamax is also just cosmetic only.

There should be rather a defined way to say what categories a pokemon is part of, which would also make "is_legendary" and "is_starter" redundant too.

[Naramsim]

Hi! Thanks for all the analysis! I cannot really participate in the discussion since I don't follow Pokemon that much anymore, especially new generations. All the forms/species data come from Veekun, and there (veekun/pokedex) are the answers about why they had these differences. Most probably now, as you said, the Pokemon data have undergone significant changes and the structure we use isn't appropriate anymore.