0

I am trying to learn more about Relational Databases. So, I did a pokemon database gathering the stats available on the Smogon website. I have done some research thus far and would like to get some insight on my current Database Design and Normalization. What are your thoughts and suggestions on my current Database Design ?

Database Design

Here is my database model, which I managed to do via the informations below.

mcd

Database Design V2

I updated my database design with the following modifications:

  • Removed table : « SPREADS »
  • Removed table : « CHECKS_COUNTERS »

  • Removed table : « LEADS »

  • Modification of the entity « POKEMON_MOVESET_SPREADS » to integrate the data from the table « SPREADS ».

  • Modification of the entity « POKEMON_MOVESET_CHECKS_COUNTERS » to integrate the data from the table « CHECKS_COUNTERS ».
  • Modification of the entity « TIERS_ASSOCIATION » to integrate the data from the « LEADS » table.

  • Modification of relations « POKEMON_MOVESET_ »* (0,N) in (1,N)

  • Addition of name alias to the « TIERS » and « GENERATION » table.

MCDV2

I think there is a problem between « TIERS_ASSOCIATION » and « POKEMON_MOVESET ». Because, I think the table « POKEMON_MOVESET » should not exist but how to make the association with « TIERS_ASSOCIATION » and the other entities (« POKEMON_MOVESET_* »)

Informations:

First, in order to obtain the data, it is necessary to choose several values:

  • Date : The file date (1 file per month for each category)
  • Level : Player level (0, 1500, 1630, ...)
  • Tier : The tier in which the player plays (UU, OU, UBER, ...)
  • Generation : The generation of pokemon (1Gen, ..., 8Gen)

There are therefore 4 essential steps before having the data.

Once all the previous data has been selected, it is possible to obtain the information in the table below:

  • Only the "Usage %" data is interesting (tas_percent in my DB design)
 + ---- + ------------------ + --------- + ------- + ------- + ------- + ------- + 
 | Rank | Pokemon            | Usage %   | Raw     | %       | Real    | %       | 
 + ---- + ------------------ + --------- + ------- + ------- + ------- + ------- + 
 | 1    | Dragapult          | 40.61997% | 1335515 | 37.505% | 1003505 | 36.128% | 
 | 2    | Corviknight        | 37.54771% | 995958  | 27.969% | 785209  | 28.269% | 
 | 3    | Clefable           | 31.23795% | 661575  | 18.579% | 530840  | 19.111% | 
 | 4    | Seismitoad         | 30.49124% | 716229  | 20.113% | 611845  | 22.028% | 
 | 5    | Excadrill          | 25.75186% | 930790  | 26.139% | 732315  | 26.365% |

But we can also get the moveset, like this:

 +----------------------------------------+ 
 | Dragapult                              | 
 +----------------------------------------+----------------------------------------+
 | Raw count: 1424591                     | Moves                                  |
 | Avg. weight: 0.0449491556183           | Draco Meteor 53.768%                   |
 | Viability Ceiling: 88                  | Shadow Ball 49.102%                    |
 +----------------------------------------+ U-turn 42.770%                         |
 | Abilities                              | Dragon Darts 35.307%                   |
 | Infiltrator 86.702%                    | Fire Blast 32.237%                     |
 | Clear Body 10.261%                     | Phantom Force 26.490%                  |
 | Cursed Body  3.037%                    | Dragon Dance 25.111%                   |
 +----------------------------------------+ Thunderbolt 24.562%                    |
 | Items                                  | Substitute 22.935%                     |
 | Choice Specs 36.130%                   | Flamethrower 16.254%                   |
 | Leftovers 25.054%                      | Hex 16.005%                            |
 | Life Orb 16.426%                       | Will-O-Wisp 15.322%                    |
 | Choice Scarf  8.741%                   | Sucker Punch 13.483%                   |
 | Choice Band  6.208%                    | Steel Wing  6.940%                     |
 | Expert Belt  2.294%                    | Other 19.713%                          |
 | Light Clay  0.719%                     |----------------------------------------+ 
 | Other  4.428%                          | Teammates                              |
 +----------------------------------------+ Obstagoon +3.156%                      |
 | Spreads                                | Cloyster +2.606%                       |
 | Timid:0/0/0/252/4/252 34.595%          | Bisharp +1.901%                        |
 | Adamant:0/252/0/0/4/252 12.020%        | Lucario +1.416%                        |
 | Modest:0/0/0/252/4/252 11.202%         | Grimmsnarl +1.413%                     | 
 | Jolly:0/252/0/0/4/252  8.562%          | Shuckle +1.023%                        |
 | Timid:96/0/16/184/0/212  2.865%        | Mew +0.958%                            |
 | Timid:0/0/4/252/0/252  2.324%          | Excadrill +0.515%                      |
 | Other 28.433%                          |                                        |
 +----------------------------------------+----------------------------------------+ 
 | Checks and Counters                                                             | 
 | Grimmsnarl 72.094 (77.31±1.30) (10.3% KOed / 67.0% switched out)                | 
 | Bisharp 70.970 (76.61±1.41) (27.2% KOed / 49.5% switched out)                   | 
 | Sylveon 70.531 (74.48±0.99) (15.0% KOed / 59.5% switched out)                   |           
 +---------------------------------------------------------------------------------+

Moveset

The moveset depends on the 4 essential steps mentioned above. It is made up of several pieces of information:

  • Abilities : There are several abilities available with a different percentage.

  • Items : There are several items available with a different percentage.

  • Moves : There are several attacks moves available with a different percentage.

  • Teammates : A pokemon can be used with one or more other pokemon.

  • Checks and Counters (Cac) : A pokemon can have 0 or more pokemon CaC.

  • Spreads :

A little more complicated but a pokemon has a preferential nature for combat (Timid, Adamant, ...). In addition, it is necessary to distribute skill points (PV, Atk, Def, Atk Spe, Def Spe, Vitesse). The percentage represents people's preferred choice.

Like this : [nature] [pv/atk/def/atk spe/def spe/vit] [percentage of use]

 +----------------------------------------+----------------------------------------+
 | Spreads                                |  Other Example :                       |
 | Timid:0/0/0/252/4/252 34.595%          |  Impish:252/0/76/0/180/0 10.527%       |
 | Adamant:0/252/0/0/4/252 12.020%        |  Careful:252/0/4/0/252/0  7.727%       |
 | Modest:0/0/0/252/4/252 11.202%         |  Impish:252/0/252/0/4/0  4.331%        |
 | Jolly:0/252/0/0/4/252  8.562%          |  Careful:248/0/8/0/252/0  4.212%       |
 | Timid:96/0/16/184/0/212  2.865%        |  Impish:252/4/252/0/0/0  3.760%        |
 | Timid:0/0/4/252/0/252  2.324%          |  Careful:252/4/0/0/252/0  3.324%       |
 | Other 28.433%                          |  Other 66.118%                         |
 +----------------------------------------+----------------------------------------+
Improve this question
1
  • Write the SELECTs; that will help you flesh out the schema. And it may help you focus on where you may be having problems.
    – Rick James
    Feb 25, 2020 at 5:34

2 Answers 2

Reset to default
0

For a first question this is quite detailed and asks a little too much at once.

In general the core tables look ok.

The number of tables with a single column, or a single column and an auto increment, particular those connected to POKEAMON_MOVEMENT_* single element, maybe could be reduced, potentially along with their joining table.

I haven't tried to understand these so can't offer a simplification. Start writing the queries and you might see what the simplification is.

Welcome to DBA, but please ask more focused questions next time.

Improve this answer
1
  • Thank you, I deleted "SPREADS" and "CHECKS_COUNTERS" tables. I will update my database design.
    – Pilipe
    Feb 25, 2020 at 13:07
0

Tiers_association seems to be over-normalized. Most importantly, do not normalize "continuous" values, such as dates, ints, and doubles.

Why are there so many "percent DOUBLE" values. Are they in many-to-many tables? If so, I don't see the pair of ids. In "1,n", it is not obvious which of the links is the "1" and which is the "n".

relation1 -- You do not need an extra table to represent a 1:1 relationship. Simply combine the tables.

1:many (1,n and 0,n) -- simply have the id of the "1" in the "n" table.

Improve this answer
4
  • - Yes, I told myself the same thing for the table "TIER_ASSOCIATION", but I must be able for example to choose a file date containing all the statistics for the month. Do you think I should put "TIERS_DATE" and "TIERS_LEVEL" in "TIERS_ASSOCIATION"? So to select only the available levels or dates, should I do a "GROUP BY" with this modification? - Yes, these are many-to-many tables. It has a lot of percentage because they are important values ​​for statistics. It is with this values that we will be able to see people's preferences. - I will delete relation1.
    – Pilipe
    Feb 25, 2020 at 13:07
  • Is a tier_date a DATE? Is tier_level a number? So, WHERE tier_date BETWEEN ... or WHERE tier_level = 3
    – Rick James
    Feb 25, 2020 at 22:20
  • Okay, to sort through the data whith the WHERE. But what if I only want the dates that exist (to display them in the form)? I am obliged to make a GROUP BY in the associative table TIERS_ASSOCIATION (which can contain between 10,000 to 20,000 data). Is it more optimizing to do a function in the code that displays the dates than having to join later for each "TIERS_ASSOCIATION"? And so in this case put the date in "TIERS_ASSOCIATION". The dates are in this form: smogon.com/stats
    – Pilipe
    Feb 26, 2020 at 7:59
  • Going through 20K rows is fast enough. Be sure to convert those dates to 'yyyy-mm-dd hh:mm:ss' before feeding to MySQL.
    – Rick James
    Mar 2, 2020 at 2:31

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.