# Strange Database Mapping Question **URL:** \ **Category:** Rails\ **Created:** [May 14, 2006, 8:35am UTC](https://www.ruby-forum.com/t/strange-database-mapping-question/60465 "2006-05-14T08:35:28Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![David\_Latham](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/9e8a1a/32.png) [@David\_Latham](https://www.ruby-forum.com/u/David_Latham)\ **Post date:** [May 14, 2006, 8:35am UTC](https://www.ruby-forum.com/t/strange-database-mapping-question/60465/1 "2006-05-14T08:35:28Z")
Dear Rails L., I am new on the list so will assume straight away that this question has been answered before. As I can see no immediate way of searching the list, I will ask the question. I apologize in advance for any disruption this may cause. Question time: I am trying to write a web application for the staff at my office to play a form of virtual Super14. Its a bit like any of the virtual sports available on the internet except this one is for Rugby. The relevant tables in my database are as follows: create table teams ( id INT AUTO\_INCREMENT NOT NULL, name VARCHAR(40), PRIMARY\_KEY(id) ); create table games ( id INT AUTO\_INCREMENT NOT NULL, date DATETIME, #Date of game home\_teams\_id INT, #id of home team away\_teams\_id INT, #id of away team home\_team\_score INT, #this is not meant to link to another table away\_team\_score INT, #this is not meant to link to another table PRIMARY\_KEY (id) ); In this example the home and away team records in games are supposed to link to a team record in teams. In the models, I was thinking that the game model would include, “has\_many :teams” and the team model would include, “belongs\_to :game” When I then look at the scaffold list for Games the home and away teams do not show up. Is there a better way to name the home and away team\_id’s in the games table that will fix this? or is there another more elegant way to handle this. Thanks for your patience reading this and I look forward to hearing from you, Dave Latham @NewZealand.Auckland ---
**Author:** ![David\_Latham](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/9e8a1a/32.png) [@David\_Latham](https://www.ruby-forum.com/u/David_Latham)\ **Post date:** [May 14, 2006, 9:04am UTC](https://www.ruby-forum.com/t/strange-database-mapping-question/60465/2 "2006-05-14T09:04:52Z")
David Latham wrote: > I am trying to write a web application for the staff at my office to > play a > form of virtual Super14. Its a bit like any of the virtual sports > available > on the internet except this one is for Rugby. > > The relevant tables in my database are as follows: > > create table teams ( > id INT AUTO\_INCREMENT NOT NULL, > name VARCHAR(40), > PRIMARY\_KEY(id) ); > > create table games ( > id INT AUTO\_INCREMENT NOT NULL, > date DATETIME, #Date of game > home\_teams\_id INT, #id of home team > away\_teams\_id INT, #id of away team > home\_team\_score INT, #this is not meant to link to another table > away\_team\_score INT, #this is not meant to link to another table > PRIMARY\_KEY (id) ); > > In this example the home and away team records in games are supposed to > link > to a team record in teams. > > In the models, I was thinking that the game model would include, > “has\_many :teams” > and the team model would include, “belongs\_to :game” > > When I then look at the scaffold list for Games the home and away teams > do not > show up. > > Is there a better way to name the home and away team\_id’s in the games > table > that will fix this? > or is there another more elegant way to handle this. The problem you’ve run into is that you have two relationships from game to team, one for home team and one for away team. Rails can’t automagically figure out what to do, so you have to tell it a bit more than usual. You’ve also got the has\_many and belongs\_to methods in the wrong classes, which is a common newbie mistake. But first, change the foreign keys in games to be singular, home\_team\_id and away\_team\_id - then you can call them game.home\_team and game.away\_team in your code. class Game \< AR belongs\_to :home\_team, :class\_name =\> “Team”, :foreign\_key =\> “home\_team\_id” belongs\_to :away\_team, :class\_name =\> “Team”, :foreign\_key =\> “away\_team\_id” end Following your schema, a team has both home games and away games, and the two sets are distinct. The set of all games for a team ends up being a union of both home and away games. class Team \< AR has\_many :home\_games, :class\_name =\> “Game”, :foreign\_key =\> “home\_game\_id” has\_many :away\_games, :class\_name =\> “Game”, :foreign\_key =\> “away\_game\_id” def games home\_games + away\_games end end That should be enough to get you started. If you want to get a bit tricky, since your Game class can act as a join model, you could set up a has\_many :through association on Team to conveniently access all its home or away opponents. has\_many home\_game\_opponents, :through =\> home\_games, :source =\> :away\_team has\_many away\_game\_opponents, :through =\> away\_games, :source =\> :home\_team Those associations may not be all that useful as such, but you can use them as a base to add on other conditions. (Just a caveat… the above code isn’t tested and it’s late here so take it with a grain of salt. But I’m pretty sure it should work ok.) – Josh S. [http://blog.hasmanythrough.com](http://blog.hasmanythrough.com) ---
**Author:** ![David\_Latham](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/9e8a1a/32.png) [@David\_Latham](https://www.ruby-forum.com/u/David_Latham)\ **Post date:** [May 14, 2006, 9:05am UTC](https://www.ruby-forum.com/t/strange-database-mapping-question/60465/3 "2006-05-14T09:05:01Z")
David Latham wrote: > When I then look at the scaffold list for Games the home and away teams > do not > show up. > > Is there a better way to name the home and away team\_id’s in the games > table > that will fix this? > or is there another more elegant way to handle this. > > Thanks for your patience reading this and I look forward to hearing from > you, > > Dave Latham > @NewZealand.Auckland Scaffolding doesn’t handle this, you’ll have to code it yourself. I suggest you look at [www.rubyonrails.com](http://www.rubyonrails.com) for some tutorials to get you started, if you haven’t already. j`ey [http://www.eachmapinject.com](http://www.eachmapinject.com) ---
**Author:** ![David\_Latham](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/9e8a1a/32.png) [@David\_Latham](https://www.ruby-forum.com/u/David_Latham)\ **Post date:** [May 14, 2006, 5:02pm UTC](https://www.ruby-forum.com/t/strange-database-mapping-question/60465/4 "2006-05-14T17:02:30Z")
Josh S. wrote: > class Team \< AR > has\_many :home\_games, :class\_name =\> “Game”, :foreign\_key =\> > “home\_game\_id” > has\_many :away\_games, :class\_name =\> “Game”, :foreign\_key =\> > “away\_game\_id” > > def games > home\_games + away\_games > end > end I knew I’d muff something doing a posting just before bed. Of course those foreign keys should be team ids. has\_many :home\_games, :class\_name =\> “Game”, :foreign\_key =\> “home\_team\_id” has\_many :away\_games, :class\_name =\> “Game”, :foreign\_key =\> “away\_team\_id” – Josh S. [http://blog.hasmanythrough.com](http://blog.hasmanythrough.com)