{"action":"opened","issue":{"url":"https://api.github.com/repos/veekun/pokedex/issues/237","repository_url":"https://api.github.com/repos/veekun/pokedex","labels_url":"https://api.github.com/repos/veekun/pokedex/issues/237/labels{/name}","comments_url":"https://api.github.com/repos/veekun/pokedex/issues/237/comments","events_url":"https://api.github.com/repos/veekun/pokedex/issues/237/events","html_url":"https://github.com/veekun/pokedex/issues/237","id":317888145,"number":237,"title":"Refactor pokemon_moves","user":{"login":"magical","id":175539,"avatar_url":"https://avatars0.githubusercontent.com/u/175539?v=4","gravatar_id":"","url":"https://api.github.com/users/magical","html_url":"https://github.com/magical","followers_url":"https://api.github.com/users/magical/followers","following_url":"https://api.github.com/users/magical/following{/other_user}","gists_url":"https://api.github.com/users/magical/gists{/gist_id}","starred_url":"https://api.github.com/users/magical/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/magical/subscriptions","organizations_url":"https://api.github.com/users/magical/orgs","repos_url":"https://api.github.com/users/magical/repos","events_url":"https://api.github.com/users/magical/events{/privacy}","received_events_url":"https://api.github.com/users/magical/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-04-26T06:00:21Z","updated_at":"2018-04-26T06:00:21Z","closed_at":null,"author_association":"OWNER","body":"The pokemon_moves table is the largest table in the pokedex by far, both in terms of number of rows (currently 465817!) and size of the csv file. It's also growing at a quadratic rate. It would be nice to shrink it down a bit.\r\n\r\nThe best idea I've come up with is to introduce the concept of a _moveset_. A moveset is a set of moves. A pokemon's _move pool_ is the union of all its movesets. This is motivated by the fact that move pools tend to change slowly over time, and different move methods change at different rates. For example, tutored moves usually change significantly in every version, whereas egg moves tend to be relatively static - only only adding moves now and then and rarely removing them. TM moves tend to change a lot between generations but not so much between versions, and so on.\r\n\r\nSpecifically pokemon_moves is currently defined as\r\n\r\n```\r\npokemon_moves\r\n    pokemon_id -> pokemon.id\r\n    version_group_id -> version_groups.id\r\n    move_id -> moves.id\r\n    pokemon_move_method_id -> pokemon_move_method.id\r\n    level integer nullable\r\n    order integer nullable\r\n```\r\n\r\nI propose we remove it and add three new tables.\r\n\r\n```\r\npokemon_moveset_map\r\n    pokemon_form_id -> pokemon_form.id\r\n    version_group_id -> version_groups.id\r\n    moveset_id -> pokemon_moveset.id\r\n\r\npokemon_movesets\r\n    id integer primary key\r\n    pokemon_species_id -> pokemon_species.id\r\n    pokemon_move_method_id -> pokemon_move_method.id\r\n\r\npokemon_moveset_moves\r\n    moveset_id -> pokemon_movesets.id\r\n    move_id -> moves.id\r\n    level integer nullable\r\n    order integer nullable\r\n```\r\n\r\nThe pokemon_moveset_moves table, which is roughly equivalent to the old pokemon_moves table, is one column smaller. It should also have fewer rows because we can now reuse movesets.\r\n\r\nNote that this proposal eschews the pokemon table in favor of linking directly to the pokemon_forms table. We can do that because forms can share movesets, meaning that we won't have to duplicate thousands of rows in pokemon_moves for aesthetic-only forms. The pokemon_movesets.pokemon_species_id column is mostly for documentation purposes, but i also don't think it would be very useful to allow sharing movesets across species.\r\n\r\nISTR doing a rough analysis a while ago of how much space this would save, but I'll have to find it again.\r\n"}}
{"actor":{"display_login":"magical"}}