Modelling Relationships Between Like Items

[psidefect]

What is the best way to model a relationship between two items from the same table?

For example, if I were to represent a football game in my db, the game table would have references to two teams (probably something like homeTeam_id and awayTeam_id). If rails is setting up it's modeling based on a naming convention, how does one model this type of relationship?

It seems similar to a has and belongs to many relationship, but with two of the same type of item. How do you handle this with Ruby on Rails?

[bluetechnx]

The short answer (since I typed this once and lost the post getting some invalid thread id bs):

create a table for either home_team or away_team. Have it id all teams. You can use this table twice for home or away so there isn't data duplication.

In the game table have:
id int(8) primary key; # Every game is unique
away_team_id int(8); # a reference to away team for this game
foreign key (home_team_id) references away_team(id);

home_team_id is just another refernece to a team (or think of it as a team name).

You don't really need to muck with has_many or such relationships in this case as the schema sets up the relationships ok.

[psidefect]

Do you mean to formally define home_team_id as a foreign key?

I'm new to Ruby and Rails. I thought the rails modeling infered the relationships between tables by mapping <singular_form_of_tblname>_id to tblname.id. Which would work (I believe) for the away_team_id => away_teams.id relationship in your example, but I'm still confused about how the home_team_id => away_teams.id relationship is made by rails.

In other languages it could be named anything as it would basically be a matter of remembering what table's key the column referred to, but how do I set it up so rails (ActiveRecord?) understands me?

Will defining it as a foreign key in the db be enough for rails to make the correct model relationships?

Thanks for the help. I'm still trying to wrap my head around the rails relationships.

[bluetechnx]

Do you mean to formally define home_team_id as a foreign key?

I'm new to Ruby and Rails. I thought the rails modeling infered the relationships between tables by mapping <singular_form_of_tblname>_id to tblname.id. Which would work (I believe) for the away_team_id => away_teams.id relationship in your example, but I'm still confused about how the home_team_id => away_teams.id relationship is made by rails.

In other languages it could be named anything as it would basically be a matter of remembering what table's key the column referred to, but how do I set it up so rails (ActiveRecord?) understands me?

Will defining it as a foreign key in the db be enough for rails to make the correct model relationships?

Thanks for the help. I'm still trying to wrap my head around the rails relationships.

Ok the way I understand "model relationships" in ActiveRecord is that AR allows you to specify the cardinality for entities and any constraints thereare. So AR will help you say this relationship is 1 to 1, or 1 to N, or N to N. That's the "has many" -> "belongs to", or "hmabtm" relationships that are usually specified in AR.

Perhaps this will make more sense to you.

You *could* do this for your example:


# DB schema for tables to hold listings of team names for both home/away team...both hold all team names, this is slightly redundant.
create table HomeTeam
id int(8);
varchar(32) name;
primary key id;

create table AwayTeam
id int(8);
varchar(32) name;
primary key id;


Rails will see "id" and know it is the primary key for each table, respectavily.

You could then create a Game table as follows:

# A game has a unique id and two opposing teams.
create table Game
id int(8);
away_team_id int(8)
home_team_id int(8)


Rails infers that away_team_id referces the AwayTeam table, and vice versa for home_team_id.

I don't see any 1..N or N..N relationships here, so we don't need has_many or belongs_to anywhere in the Active Record code.

We do need some active record to tell Rails that these Models exist. Rails can infer the rest from our naming schemes (Model -> Schema) and can link the tables because of our _id usage everywhere in the schemas.

So the AR is simply:

# HomeTeam.rb
class HomeTeam < ActiveRecord::Base
end

#AwayTeam.rb
class AwayTeam ActiveRecord::Base
end

#Game.rb
class Game < ActiveRecord::Base
end