Data Model

Above is a relatively simple representation of the data model that will be implemented in SQLite by the bot. As shown above, there are two tables:

1. users: Will contain information about registered users
2. pokemon: Will contain information about Pokemon that a user has caught

The important relationship here is that the primary key of the users table is a foreign key in the pokemon table. The reason we want to use a foreign key here is that we want to make sure that only registered users/trainers are able to catch Pokemon. This is because the bot is opt-in. Users need to express interest in using the bot for us to store any data. The reason is that while we don't store much user data, we still store aspects such as their user name. We may also wish to add more functionality to the bot later that would require us to store additional user information or statistics. This is a key part of our work to ensure user registration for the bot.

users Table

First, lets look at the users table. It has 3 fields:

• user_id: String
  • Information that uniquely identifies a user. This is provided to us along with the message content for every Discord message the bot processes.
• create_date: Integer
  • Date the user registered with the bot.
• last_action_date: Integer
  • Date the user last performed an action with the bot. This could be registering, catching (or failing to catch) a Pokemon, listing caught Pokemon, etc...

The need for the user_id is fairly self explanatory - we need to separate information based on user. Each user is also unique, therefore it makes sense to have this value as the primary key for this table.

The create_date is really here just to provide some information about when the user registered with the bot. At this time, it has no expected user. However, since this is information we can only collect at registration time we are recording it for future (potential) use.

Finally, last_action_date defines the last time the user interacted with the bot. The reason this field exists is so that we may do housekeeping on our database. Specifically, if a user is not actively using the services the bot provides, then we don't need to store the users data. For inactive users we can remove their information in all related tables thereby saving space.

Both create_date and last_action_date are Integer types because we don't need the extra granulatiry of hours, minutes, or seconds. We can get by with the date format YYYYMMDD. These will always be increasing (each day). The dates themselves should only take up 8 bytes, though SQLite can use different size integers.

pokemon Table

The pokemon table has 4 fields:

• id: Integer or String
  • The ID of this record in the table. It can be set manually or through use of a autoincrement. If set manually, it should be something like a UUID. The UUID would take up more storage space than the autoincrement, but the autoincrement will require more overhead and increase latency. In this project we will use the autoincrement feature. However, if you fork this project feel free to use UUID.
• trainer_id: String
  • This is a foreign key that matches the user_id primary key from the users table. This exists so that we only allow keeping track of Pokemon for users that opted into the bot.
• pokemon_number: Integer
  • The Pokedex numerical value of the Pokemon. This is used to talk to the Pokemon API
• pokemon_name: String
  • Name of the Pokemon was caught. This is for more efficient queries so we don't have to always talk to the Pokemon API for specific bot commands (such as list when a user wishes to list all their caught pokemon).
• pokemon_level: Integer
  • Level of the Pokemon that was caught. This is a value that is randomly assigned when the Pokemon is caught and is not provided at the Pokemon API level.

While the id for this table is not directly used, there are some features that are being considered which may utilize this information later. As an example, there may be a feature around leveling up a Pokemon. If this gets implemented, then we need to be able to update a specific row in this table. At that time, this field will be used.

The integer fields pokemon_number and pokemon_level are relatively small integer values. pokemon_number for instance is only supported up to number 807, and pokemon_level only goes up to 100. As a result, according to the SQLite documentation, it should store them in fewer bytes.