How did my old create joined tables work with no indexes?

Question

I previously created a joined table with the migration:

class CreateJoinTableCategoryListing < ActiveRecord::Migration[5.2]
  def change
    create_join_table :categories, :listings do |t|
      # t.index [:category_id, :listing_id]
      # t.index [:listing_id, :category_id]
    end
  end
end

I was looking back on this as i am going to be creating a new join tables. But while looking at it i noticed i migrated with the t.index's still commented out and the joined tables still operate correctly.

I read into this and i haven't found any posts about either someone doing the same or not needing them.

How is it operating with those index's never migrated and how needed are they?

I am creating a new migration:

class CreateJoinTable < ActiveRecord::Migration[5.2]
  def change
    create_join_table :users, :affiliates do |t|
      # t.index [:user_id, :affiliate_id]
      # t.index [:affiliate_id, :user_id]
    end
  end
end

Which index should i be choosing here?

How it should work is that an affiliate is able to manually submit a "commission" to the table (which does need to be added to the migration), but if the commission is updated, it should take the place of the column and not create a new row.

A user will have really nothing to do with this and will be mostly updated by the affiliate to update the commission rates they have on the user.

Update:

Is it even possible to add another field to the join table?

I wanted to add a :commission to the table but i can't find any docs to do anything for that. Should i just be defining the commission rate within the users table and do away with the join table?

UPDATE 2:

Ended up scratching this idea and keeping my current method of doing it with the users and affiliates association only. I did away with the UsersAffiliates idea as it's not needed for this case.

Answer 1

How is it operating with those index's never migrated and how needed are they?

All types of assocations in Rails will work without indices. The only thing that is required is that the correct tables and columns exist.

Indices are however critical for performance as the size of your database grows. They also provide constraints such as uniqueness that ensure that duplicate data cannot be inserted due to race conditions.

Which index should i be choosing here?

The whole reason that Rails generates two different indices is that you should choose the index that cooresponds to how you will most often be searching the table. If you are most often using User.joins(:affilitates) you would choose t.index [:user_id, :affiliate_id] for example.

How it should work is that an affiliate is able to manually submit a "commission" to the table (which does need to be added to the migration).

The create_join_table macro creates a join table named for has_and_belongs_to_many assocations.

The main problem with has_and_belongs_to_many assocations is that they are headless. There is no model and therefore no way to query the table directly or add additional metadata columns.

What you instead want is a has_many :through association.

class User < ApplicationRecord
  has_many :user_affiliates
  has_many :affiliates, through: :user_affiliates
end

class Affiliate < ApplicationRecord
  has_many :user_affiliates
  has_many :affiliates, through: :user_affiliates
end

# call this whatever you want
class UserAffiliate  < ApplicationRecord
  belongs_to :user
  belongs_to :affilitate
end

While has_and_belongs_to_many uses the table naming scheme users_affilities (plural_plural) you want to use user_affilities for a has_many through: association.

You can fix this by:

1. Just generating the table/model through the normal generator rail g model user_affiliate.
2. If the table exists write a migration to rename the table.

but if the commission is updated, it should take the place of the column and not create a new row.

You can solve this by:

1. Add a unique compound index on the two columns t.index [:user_id, :affiliate_id], unique: true.
2. Add a uniqueness validation in the join model. validates_uniqueness_of :user_id, scope: :affiliate_id.
3. Use .find_or_initialize_by in your controller to update an existing row if it exists instead of creating a new row if one already exists.