Module 3 - Normalize, generate migrations and validate models with Rails

Let’s say our client want us to create an API REST focused on Pokemons and Trainers, but we don’t have any data yet. We only know we need to track a trainer’s name, gender, age, home region, their team member status (that could be ‘active’ or ‘inactive’) and the pokemons he/she has. Also every trainer owns a battle record, that is a win and loss count. Besides we’re tracking a pokemon through its name, base experience, main type, main ability and its trainer.

For example one trainer called Satoshi who is a 10 years old boy from Pallet Town and an active team member. He’s captured many pokemons (Pikachu, Charmander, Squirtle, etc.) and has won 35 and lost 54 battles.

Design our database

By now our client didn’t deliver us any data yet. So we’re designing our database based on conversations with our client.

• First, we need to identify our entities. At first sight and to keep it simple, we’ll only have 2 entities: Trainers and Pokemons.
• Second, we identify relationships. One trainer can have many pokemons and one type of pokemon can be owned by many trainers. So we have a many-to-many relationship.
• Third, we identify and associate attributes. From what we know, a trainer has a name, a gender, an age, a region, a team_member, wins and losses. And a pokemon has a name, a base_experience, a main_type and a main_ability.
• Fourth, we determine attribute domains. Those are sets of values allowed in an attribute or more simple, our data types. As we’re using Ruby On Rails and it has its own data types, we’ll have in Trainers a string for name, string for gender, integer for age, string for region, string for team_member, integer for wins and integer for losses. And in Pokemons a string for name, integer for base_experince, string for main_type and string for main_ability.
• Fifth, we set our primary and foreign keys. Normally, we’d have the next Entity Relationship Diagram.

But thank God Rails takes care of this, so we don’t need to worry much about this. Here is our ERD with Rails associations.

Our client whose name coincidentally is Oggey is exhorting us to work with the database when we don’t have any data yet. As good developers we are, we start working with Rails migrations. After that a magic happens and our client finally retrieves us the data we need in a CSV file like this.

t_name,t_gender,t_region,t_team_member,name,main_ability,main_type,base_exp,wins,losses
Olin,male,Pacifidlog Town,false,gloom,chlorophyll,poison,138,49,31
Olin,male,Pacifidlog Town,false,blastoise,rain-dish,water,239,49,31
Olin,male,Pacifidlog Town,false,growlithe,intimidate,fire,70,49,31
Ashlyn,female,Ambrette Town,true,vileplume,chlorophyll,poison,221,42,40
Ashlyn,female,Ambrette Town,true,venusaur,overgrow,poison,236,42,40
Ashlyn,female,Ambrette Town,true,pidgeot,tangled-feet,normal,216,42,40
Zane,male,Mahogany Town,false,hypno,insomnia,psychic,169,34,26
Zane,male,Mahogany Town,false,alakazam,magic-guard,psychic,225,34,26
Zane,male,Mahogany Town,false,tentacool,clear-body,water,67,34,26
⋮

• Sixth, we check the models for redundancy. We examine one-to-one relationships. Later we try to remove redundant data or avoid to store the same data in more than one place. For this we can use some tools like Google Sheets to check and filter redundant records or make a script to generate new CSV files with unique records or just use Ruby on Rails.

Fot the last point, we’ll use Rails for normalizing our data with its model methods and seeds file later, so no need to worry yet. In case it’s a really big data we may use some SQL command for sulk seeding, e.g. PostgreSQL has the COPY method to insert big data and Ruby has the PG gem.

Generate migrations

Previously, we had to work with some migrations based on conversations with our clients. There are two ways to do that, one is generating explicitly the migration, but with that we’d need to generate the model too. So we’re going to generate the model and Rails will automatically generate the migration too.

$ rails generate model Trainer name:string gender:string age:integer region:string team_member:string wins:integer losses:integer

And we have the following files: a migration and a model respectively.

# db/migrate/20200523XXXXXX_create_trainers.rb
class CreateTrainers < ActiveRecord::Migration[6.0]
  def change
    create_table :trainers do |t|
      t.string :name
      t.string :gender
      t.integer :age
      t.string :region
      t.string :team_member
      t.integer :wins
      t.integer :losses

      t.timestamps
    end
  end
end

# app/models/trainer.rb
class Trainer < ApplicationRecord
end

We do the same for our pokemons.

$ rails generate model Pokemon name:string main_ability:string main_type:string base_exp:integer

With that we’ve created the following files.

# db/migrate/20200523XXXXXX_create_pokemons.rb
class CreatePokemons < ActiveRecord::Migration[6.0]
  def change
    create_table :pokemons do |t|
      t.string :name
      t.string :main_ability
      t.string :main_type
      t.integer :base_exp

      t.timestamps
    end
  end
end

# app/models/pokemon.rb
class Pokemon < ApplicationRecord
end

We create our database and apply the migrations with the following commands.

$ rails db:create
$ rails db:migrate

We tested that is working correctly and we’re able to insert data manually through Rails console, so it’s ok. But now our client has given us the real data and we have to remove the age column and change the team_member column type from our trainers relation.

To do that, we have to create two more migrations. First, we remove age from trainers.

$ rails generate migration RemoveAgeFromTrainers age:integer

With that we have the next file.

# db/migrate/20200523XXXXXX_remove_age_from_trainers.rb
class RemoveAgeFromTrainers < ActiveRecord::Migration[6.0]
  def change
    remove_column :trainers, :age, :integer
  end
end

Second, we change team_member type from trainers. Rails doesn’t have a command to generate a migration like that. So once created we’ll have the add the methods to our migration manually.

$ rails generate migration ChangeTeamMemberOnTrainers

We have the next file.

# db/migrate/20200523XXXXXX_change_team_member_on_trainers.rb
class ChangeTeamMemberOnTrainers < ActiveRecord::Migration[6.0]
  def change
  end
end

Changing a column type is an irreversible method for Rails, so we need to define an up and down methods to make our migrations reversible and have our migration file like this.

# db/migrate/20200523XXXXXX_change_team_member_on_trainers.rb
class ChangeTeamMemberOnTrainers < ActiveRecord::Migration[6.0]
  def up
    change_column :trainers, :team_member, 'boolean USING CAST(team_member AS boolean)'
  end

  def down
    change_column :trainers, :team_member, :string
  end
end

This way, when to migrate our database, it will change the team_member column type from string to boolean and when we rollback, it’ll revert its data type to string.

One last step would be to create our many-to-many relationship between trainers and pokemons. We achieve this just by creating a join table with Rails migrations and adding the method hasandbelongstomany inside our models.

$ rails generate migration CreateJoinTableTrainersPokemons trainer pokemon

We uncomment the statements inside the change method.

# db/migrate/20200523XXXXXX_create_join_table_trainers_pokemons.rb
class CreateJoinTableTrainersPokemons < ActiveRecord::Migration[6.0]
  def change
    create_join_table :trainers, :pokemons do |t|
      t.index [:trainer_id, :pokemon_id]
      t.index [:pokemon_id, :trainer_id]
    end
  end
end

And add the hasandbelongstomany method in our models which let Rails use instance methods by our models.

# app/models/trainer.rb
class Trainer < ApplicationRecord
  has_and_belongs_to_many :pokemons
end

# app/models/pokemon.rb
class Pokemon < ApplicationRecord
  has_and_belongs_to_many :trainers
end

We mustn’t forget to apply our migrations.

$ rails db:migrate

With this we’ve created our migrations and models, so now we can move on to validate our models and seed our database.

Validate models

At this moment, we have our Trainer and Pokemon models. But we need to ensure that only valid data is saved into our database. For that we set up model-level validations.

We’ll ensure every trainer has a name and a gender, his/her name must be unique, team_member must be true or false and wins and losses must be greater than zero.

# app/models/trainer.rb
class Trainer < ApplicationRecord
  has_and_belongs_to_many :pokemons
  validates :name, :gender, presence: true
  validates :name, uniqueness: true
  validates :team_member, inclusion: { in: [true, false] }
  validates :wins, :losses, numericality: {
    greater_than_or_equal_to: 0,
  }
end

We’ll also ensure every pokemon has a name, a mainability and a maintype, its name must be unique and its main_type must be one of the following: bug, electric, fairy, fighting, fire, flying, grass, ground, ice, normal, poison, psychic, rock, steel or water.

# app/models/pokemon.rb
class Pokemon < ApplicationRecord
  has_and_belongs_to_many :trainers
  validates :name, :main_ability, :main_type, presence: true
  validates :name, uniqueness: true
  validates :main_type, inclusion: { in: [
    %w[bug electric fairy fire flying grass ground ice normal poison psychic rock steel water]
  ] }
end

With these validations, we’ve ensured only valid data is passed and saved into our databases. Now we’re going to normalize and seed our database with Rails methods.

Normalize

To remove redundant data we’re using a Rails model method called findorcreate_by. This method retrieves the record from our database and if it doesn’t exist, creates it and saves it into our database. That way we ensure only unique records are passed.

Finally we’re seeding our trainers and pokemon tables with data from our CSV file. We’re also generating the many-to-many relationship between them with the << method.

# db/seeds.rb
require 'csv'

table = CSV.read('db/pokemon-data.csv', {
  headers: true,
  header_converters: :symbol,
})

table.each do |row|
  trainer = Trainer.find_or_create_by!(
    name: row[:t_name],
    gender: row[:t_gender],
    region: row[:t_region],
    team_member: row[:t_team_member],
    wins: row[:wins],
    losses: row[:losses],
  )

  pokemon = Pokemon.find_or_create_by!(
    name: row[:name],
    main_ability: row[:main_ability],
    main_type: row[:main_type],
    base_exp: row[:base_exp],
  )

  trainer.pokemons << pokemon
end

At last, we’ve seeded our database with unique records and established their relationships.

And that’s all. Thanks for reading.