DB structure

[jaicab]

Training: Training table saves all the data for a single training session.

Field	Type	Description
id	INT(11) NOT_NULL AUTO_INCREMENT	Identification number
id_url	VARCHAR(255) DEFAULT NULL	Url which allows users to load an unfinished training.
id_user	INT(11) NOT_NULL DEFAULT 0	User number. This value will be always 0 because the first version doesn't support user creation
hp	INT(3) NOT_NULL DEFAULT 0	Hp stat value
attack	INT(3) NOT_NULL DEFAULT 0	Attack stat value
defense	INT(3) NOT_NULL DEFAULT 0	Defense stat value
spattack	INT(3) NOT_NULL DEFAULT 0	Sp Attack stat value
spdefense	INT(3) NOT_NULL DEFAULT 0	Sp Defense stat value
speed	INT(3) NOT_NULL DEFAULT 0	Speed stat value
version	VARCHAR(50) NOT_NULL DEFAULT 'xy'	Pokemon 6th gen game version ('xy' -- XY Pokemon games, 'oras' -- Ruby Omega Alpha Sapphire Pokemon Games)
pokerus	TINYINT(1) NOT_NULL DEFAULT 0	Pokerus (0 -- disabled, 1 -- enabled)
power_brace	TINYINT(1) NOT_NULL DEFAULT 0	Power brace (0 -- unequipped, 1 -- equipped)
sturdy_object	TINYINT(1) NOT_NULL DEFAULT 0	Sturdy object (0 -- unequipped, 1 -- equipped)
timestamp	TIMESTAMP

Records: Records will provide us with information about how users are using the application, which version of the game is the most used and more

Field	Type	Description
id	INT(11) NOT_NULL AUTO_INCREMENT	Identification number
id_training	INT(11) NOT_NULL DEFAULT 0	This id related the record with a single training session
stat_name	VARCHAR(50)	Name of the stat what we are adding or decreasing the value
stat_value	INT(3) NOT_NULL DEFAULT 0	Amont of evs that we are adding or subtracting
id_horde	INT(11) NOT_NULL DEFAULT 0	The ID of the horde when a horde has been used during the training session
id_vitamin	INT(11) NOT_NULL DEFAULT 0	The ID of the vitamin when a vitamin has been used during the training session
version	VARCHAR(50) NOT_NULL DEFAULT 'xy'	Pokemon 6th gen game version ('xy' -- XY Pokemon games, 'oras' -- Ruby Omega Alpha Sapphire Pokemon Games)
pokerus	TINYINT(1) NOT_NULL DEFAULT 0	Pokerus (0 -- disabled, 1 -- enabled)
timestamp	TIMESTAMP

User: First approach of the User basic table. Not used in the first version.

Field	Type	Description
id	INT(11) NOT_NULL AUTO_INCREMENT	Identification number
name	VARCHAR(50) NOT_NULL DEFAULT ''	The name of the user
email	VARCHAR(255) NOT_NULL DEFAULT ''	The email address of the user
timestamp	TIMESTAMP

Please check the new database diagram, make and comment any change that you consider relevant ASAP because I'm going to install and test the DB in the server tomorrow in the morning.

Thanks.

[jaicab]

David, can you please break down the DB and move it to text instead of an image and put it at the top? Bear in mind that we need to add vitamins. Also explain what each table and field represents.

[davidguerreiro]

Sure ! About vitamins, they are displayed on the screen in the same way that hordes, so we save only the data about vitamins on the second table (records)

[jaicab]

Ok, you just need to create a list for each table in markdown to replace the image. That way we can all edit it and we have it there, centralised.

[jaicab]

Ok, I'm proposing a few changes. Let me know what you think.

• Abstract user: We're not using the user for now, but it's likely we end up having lots of features for each user and training. We should have a user table (id, email) and then a id_user reference on the training table. This would allow us to change the fields on user without affecting the SQLs in training and vice versa. It's basically more scalable.
• Change version field to gen or generation across tables. We may end up using version to refer to the version of the calculator. I would also recommend using numeric IDs for this field and not xy/oras as it would be easier to scale.
• Records has some redundancy issues: We don't need all the stats. Each record should store an operation that changes a single value by a certain amount. This change could come from killing and horde, a vitamin or a manual input. So we don't need fields for every stat. We need stat_name field to see what stat this operation refers to and a value field which can be positive or negative. We also need a id_horde field and a id_vitamin field to know if they used any of those.
• Hordes and vitamins are static tables: We shouldn't need to update these tables ever. So we don't need times_used as we can extract that counting from the records table. They should have a value field that represents how much add to their stat. We don't really need these tables at all, they could be just in the PHP, but it's worth discussing if they change what's easier to edit.

I think this way we not only avoid redundancy, but also improve scalability and our possible studies on the results.

[davidguerreiro]

Ok. let's do it point by point:

• Abstract user : Done.
• Change version field to gen or generation across tables : generation is not the proper word. We are going to use game instead because in general generation includes a group of games. For example, this calculator is working with all the VI 'gen' pokemon games.
• Records has some redundancy issues : Yes, I forgot about the first approach we had when we designed the DB, so I will consider your changes.
• Hordes and vitamins are static table : If we have an id_horde and an id_vitamin then it means that the data is saved in any other table, isn't ? If not, there is something that I don't understand. I don't know about vitamins, but hordes may change in the future with the release of the new games. Anyway, we should discuss this issue in person.

I'm updating the tables above with the new changes.

[jaicab]

I like the use of game instead of generation. Nice one.

Regarding the id_horde and id_vitamin, these go on the records table. We can have a static list in PHP or JSON (or both) that lists the hordes and vitamins. What I mean by static is that there are rarely to change, not like records and training which would constantly change. There is no need to have them in a DB because we never are going to change them with a form request or a SQL, and it's us the dev team that is going to change the data in that table.

My point is that when something is internal and it's not going to change or it's unlikely it's going to change often, it should be plain in the code and not in a DB. So, for instance we could have:

$hordes = array();

// These could be abstracted into a function
$horde_magikarp = new stdClass();
$horde_magicarp->name = "Magikarp";
$horde_magikarp->game = 'xy';
array_push($hordes, $horde_magikarp);

// Now we can just use it
echo $hordes[0]->name; // "Magikarp"

// Get the ones for the game 'xy'
function filterByGameXY($var){
    return $var->game === 'xy';
}
// Now $hordes_xy only contain the hordes that are assigned to XY trainings
$hordes_xy = array_filter($hordes, 'filterByGameXY');

All this loads much faster than getting to the DB and doing stuff.

[davidguerreiro]

Got it !! Nice explication !! You'r making magikarp useful for once in life !!

[jaicab]

Glad you got the idea. I also think it should be a JSON file with the hordes and another with the vitamins, and then we process that with PHP so it's easily available. I say JSON because we can cache it and it would be instantaneous, but giving us the flexibility to do other stuff.

[jaicab]

Also, if we feel like it, we can always move the data from the JSON file to tables in the DB and then create the JSON from it. Super scalable!

[jaicab]

I've added timestamp to the user table.

[davidguerreiro]

Thanks ! :D