Database

This section lists some of the important database conventions the team must follow. While the code examples are provided mainly in Rails/ActiveRecord, developers must implement similar practices in other frameworks like Phoenix as well.

Relational Database

• Prefer using PostgreSQL for relational data storage.

Naming

• Use snake_case for both database tables and columns. But use plural for database tables and singular for column names.

  +---------------------------+
  | campaign_locations        |
  +-------------+-------------+
  | id          | ID          |
  | name        | STRING      |
  | location_id | FOREIGN KEY |
  | updated_at  | DATETIME    |
  +-------------+-------------+
  

• Use predicate-like names for boolean database columns.

  add_column :users, :enabled, :boolean, null: false, index: true
  

  add_column :users, :is_enabled, :boolean, null: false, index: true
  

• Use the _at suffix for the database column name when adding a new date or datetime database column.

  add_column :users, :deleted_date, :date
  add_column :users, :last_sign_in_time, :datetime
  

  add_column :users, :deleted_at, :date
  add_column :users, :last_sign_in_at, :datetime
  

• Combine table names to name join tables choosing to pluralize based on the intent of the table.

  # Given these two models
  class Campaign < ApplicationRecord
    has_many :influencers, inverse_of: :campaign
  end
  
  class Influencer < ApplicationRecord
    has_many :campaigns, inverse_of: :influencer
  end
  

• With the resulting join table

  class Campaign < ApplicationRecord
    has_many :influencers, through: :campaigns_influencers
    has_many :campaigns_influencers, inverse_of: :campaign
  end
  
  class Influencer < ApplicationRecord
    has_many :campaigns, through: :campaigns_influencers
  end
  

  class Campaign < ApplicationRecord
    has_many :influencers, through: :campaign_influencers
    has_many :campaign_influencers, inverse_of: :campaign
  end
  
  class Influencer < ApplicationRecord
    has_many :campaigns, through: :campaign_influencers
  end
  

• Name the migration files to describe the operations they perform. The general template is [ChangeMade]+To/From+[TableNamePlural]. For instance, when dropping a column named surname from the table users, name of the migration will be DropSurnameFromUsers. In case when multiple fields are dropped at once, try to provide a small context info apart from table name. Eg.: DropContactDetailsFromUsers

  class DropSurnameColumn < ActiveRecord::Migration[6.0]
    def change
      remove_column :users, :surname
    end
  end
  

  class DropSurnameFromUsers < ActiveRecord::Migration[6.0]
    def change
      remove_column :users, :surname
    end
  end
  
  class DropContactDetailsFromUsers < ActiveRecord::Migration[6.0]
    def change
      remove_column :users, :phone
      remove_column :users, :email
    end
  end
  

• For data migration, use meaningful name also.

  class UpdateWebsite < ActiveRecord::Migration[6.0]
    def change
      # your changes to DB here
    end
  end
  

  class UpdateWebsiteColumnOfUsers < ActiveRecord::Migration[6.0]
    def change
      # your changes to DB here
    end
  end
  

Indexing

• Use database indexes on foreign keys and boolean columns for faster queries.

  add_column :users, :location_id, :integer, foreign_key: true, index: true
  add_column :users, :is_enabled, :boolean, null: false, default: false, index: true
  

Constraints

• Prefer to use both database constraints and validations when using libraries e.g. Ecto, ActiveRecord, etc.

  class User < ActiveRecord::Base
    validates :name, presence: true
  end
  
  create_table 'users', force: :cascade do |t|
    t.string :name, null: false
  end
  

• Use null constraint for columns that CANNOT be blank.

  create_table 'users', force: :cascade do |t|
    t.string :name, null: false
  end
  

• Use default constraint for columns with default values.

  create_table 'users', force: :cascade do |t|
    t.string :name, null: false
    t.integer :view_count, default: 0
  end
  

• Use unique constraint for columns with unique values, such as email.

  create_table 'users', force: :cascade do |t|
    t.string :name, null: false
    t.string, :email, null: false, unique: true
  end
  

• Use check constraint wherever possible for limiting a columns value.

  create_table 'products', force: :cascade do |t|
    t.integer :price, null: false
    t.check_constraint "price_check", "price > 100"
  end
  

• Define both default value and NOT NULL constraint for boolean columns to prevent the three-state boolean problem.

  add_column :users, :is_enabled, :boolean, null: false, default: false, index: true
  

Data Durability

• Always soft-delete relational data for integrity and audit purposes. This means every deleted row in the database should store a timestamp value for a column named deleted_at or discarded_at.

• For Rails, prefer to use gem discard.

Data Types

• Use the bigint or uuid data type for storing primary IDs.

• Prefer the citext data type for storing case insensitive data such as emails.

• Prefer the jsonb data type for storing object-like data over hstore and json. Settings-like or configuration-like data are good candidates to be stored as jsonb.

• Use the numeric data type for storing monetary types. The numeric type can be configured to use different precisions according to the currency in use. Do not use real or double precision types because they are inexact types.

Security

• Use prepared statements for database operations requiring user inputs to prevent SQL injections.

  User.where("last_seen_at > #{params[:start_datetime]}")
  

  User.where('last_seen_at > ?', params[:start_datetime])
  

• Use hash-based parameters for database operations requiring constants.

  User.where('status = ?', :new)
  

  User.where(status: :active)
  

• Beware of initialing objects directly based on user input. Always add a validation layer or even prefer using factories.

  class FooForm; end
  class BarForm; end
  

  form_klass = "#{params[:kind].camelize}Form".constantize
  form_klass.new.submit(params)
  

  klasses = {
    'foo' => FooForm,
    'bar' => BarForm
  }
  
  klass = klasses[params[:kind]]
  if klass
    klass.new.submit(params)
  end
  

  # The best
  class FormFactory
    def self.build(type, *args)
      case type
      when :foo
        FooForm.new(*args)
      when :bar
        BarForm.new(*args)
      end
    end
  end
  

Non-relational Database

• Prefer using Redis for key/value data storage.

• Prefer using ElasticSearch for document-oriented data storage.