Need to change relation between DB tables

Question

ok, so lets get the basics out of the way.

I'm running ruby 1.8.7, I'm using the sequel gem version '2.6.0'.

I have a table called Users and a table called Teams

Right now a user can have one team and as such it's relation is:

belongs_to :npt_team

However as part of a feature upgrade for teams I have to make it so Users can be apart of multiple teams.

What I want to know:

I can change it to one of the following:

• :has_and_belongs_to_many
• :many_to_many
• :many_to_many_by_ids

which one is the best to use and why(because I like to know)?

Second of all what will happen to the DB in the tables when I change this?

Any thing else I should be wary of/know about?

I'm using the following mysql version:

mysql Ver 14.14 Distrib 5.6.29, for osx10.11 (x86_64) using EditLine wrapper

EDIT:

Ooops forgot to mention a rather pertinent point.

I'm not using rails, I'm use an old frame work called Ramaze.

Answer 1

The answer to my question is:

to create the relationship I need to add the following to the Users table:

has_and_belongs_to_many(:npt_teams,
                        :join_table => :users_teams,
                        :class => 'NptTeam',
                        :left_key => :user_id,
                        :right_key => :npt_team_id)

many_to_many_by_ids :npt_teams, 'UsersTeams'

Create a new join table like so:

class UsersTeams < Sequel::Model
  clear_all

  set_schema {
    primary_key :id
    integer :user_id, :null => false, :default => 0
    integer :npt_team_id, :null => false, :default => 0
  }
  create_table unless table_exists?

  belongs_to :user
  belongs_to :npt_team
end

and the relationship is created along with the join table.

I don't know if this is the best way to do it but It seems to work.

As for the second question, I don't know, the data currently in the DB seems to be unaffected.

Now I just need to move the current Team to the new table and that should be it.

As for what else I might need to know well I don't, becuase you know, those that do know have seen to know have not respond so I'm just going to have to wing it.

EDIT:

script to move data across:

User.all.each do |user|
  join = UsersTeams.create(:user_id => user.id, :npt_team_id => user.npt_team_id)
  puts join.inspect
  join.save
  puts user.npt_teams.to_a.map {|t|t.inspect}.to_s
end