Ruby Forum Ruby on Rails > Looking for DB/Model Design Suggestions

Hi,

I'm working on an app - my first Rails - and am looking for some 
suggestions on the best way (or good way) to set up my models / DB. In 
general there are teams and matches; a team will participate in multiple 
matches, and in each match there will be 6 teams - 3 vs. 3.

I'm going to want to be able, for a particular team, pull up all matches 
for that team, to display a scoring summary, etc. I was thinking of two 
tables Team and Match. The team table would have their number and name, 
and other info, and the Match table would have the match number, 6 
fields for the teams involved, a field for the score for the winners and 
one for the losers, and a general comment field.

A tricky bit is I want to be able to have a Match entry page, where the 
user would enter the teams numbers, the final scores and the comments, 
and upon saving, having the appropriate updates so I can do a lookup by 
team to see their matches played.

Any hints? If more info is needed, I'll be happy to provide.

Thanks!

class Team < ActiveRecord::Base
  has_many :competitions
  has_many :matches, :through => :competitions

  def winning_matches
    winnings = Competition.find_by_winner(true)
    winnings.collect { |won| won.match }
  end
end

class Competition < ActiveRecord::Base
  belongs_to :team
  belongs_to :match
end

class Match < ActiveRecord::Base
  has_many :competitions
  has_many :teams, :through => :competitions
end

class InitialSchema < ActiveRecord::Migration
  def self.up
    create_table "team", :force => true do |t|
      t.column "name",          :string, :limit => 100
      t.column "number",        :integer
      # other info...
      t.column "created_at",    :datetime
      t.column "updated_at",    :datetime
    end

    create_table "match", :force => true do |t|
      t.column "number",        :integer
      t.column "winning_score", :integer
      t.column "losing_score",  :integer
      t.column "comment",       :text
      t.column "created_at",    :datetime
      t.column "updated_at",    :datetime
    end

    create_table "competition", :force => true do |t|
      t.column "team_id",       :integer
      t.column "match_id",      :integer
      t.column "winner",        :boolean
      t.column "created_at",    :datetime
      t.column "updated_at",    :datetime
    end
end

----------

Save the migration class as 001_initial_schema.db in /db/migrate/ and 
run
rake migrate.
Then create 3 separate model files as above. You don't really have to 
use
the :through
but it makes it easier. (You'll need edge Rails to use :through.)

I'm not entirely sure I understand your domain issues, but creating a 
3rd
table, really
just a join table made into a separate "competition" model seems clean.

team = Team.find_by_name('Hooligans')
team.matches  #  Gets an array of Match
team.winning_matches # Returns array of won Matches

Caveat emptor, given that I've just typed this in without irb, etc.
-Bill

Oops. That's what happens when you just type code straight.
#winning_matches should have a first line like:
winnings = Competition.find_all_by_team_id_and_winner(self.id, true)

Or, really,
winnings = competitions.find_by_winner(true)
Time for sleep :) ...

Thanks for that info.

I have to now figure out the rest :) (yeah, am a newbie).

More background - there will be matches, composed of two "alliances" of 
three teams (Red and Blue, for discussion sake.) Each team (anywhere 
from 25 to 50 teams) will participate in a series of matches, approx. 8 
- 10.

I was looking at it by trying to limit it to the 6 teams in a match, but 
was getting confused.

I guess in my create method, I'm going to have to pull the 6 teams in 
the match and create the appropriate items. I was thinking of using a 
third table to link, but wasn't sure how to make the link. I mocked up a 
page to help me visualize what I need to do. It has an entry for a match 
number, 3 field for the "red alliance", 3 for the "blue alliance", the 
"red" score, the "blue" score, and a comment field.

{Not to mention, I'm not sure how to set up edge Rails - I'm using 
Locomotive on OS X. I tried following the items on the wiki page: 
<http://wiki.rubyonrails.com/rails/pages/EdgeRails> - I did the check 
out to the vendor directory, but when I try to do the rake freeze_edge 
command, I get and error:

rake aborted!
Don't know how to build task 'freeze_edge'
}

Thanks again!

jt

If you are worried about restricting the entries to 6, you could add 
some
validation code via #validate_on_create, etc.
If you need to keep track of which team was "blue" or "red", that would 
go
into the competition (join) table.
I skipped the freeze_edge command and use edge rails as a subversion
external in the /vendor directory.
The logic isn't much tougher if you want to skip ":through" and edge 
rails.
-Bill

Bill Katz wrote:
> If you are worried about restricting the entries to 6, you could add 
> some
> validation code via #validate_on_create, etc.
> If you need to keep track of which team was "blue" or "red", that would 
> go
> into the competition (join) table.
> I skipped the freeze_edge command and use edge rails as a subversion
> external in the /vendor directory.
> The logic isn't much tougher if you want to skip ":through" and edge 
> rails.
> -Bill

Thanks...again.

Since I can't seem to get edge rails to work (I pulled the subversion
external into the /vendor directory, but it doesn't look like Locomotive
is using it), how would I not use the :through?

How, for instance, would I get, given a team, the matches they were in,
along with the teams for each match?

And.. how would I add a new item, given the details of a match? (sorry
for the newbie questions...)

thanks muchly.

jt

what does this do ?

:through => :competitions

i am looking to do somethign similar and couldnt follow all your code.

thanks
adam

Bill Katz wrote:

> class Match < ActiveRecord::Base
>   has_many :competitions
>   has_many :teams, :through => :competitions
> end

Looks like I'm going to have to find a new name for this model... 
"Match" is a reserved word in MySQL - I was getting an error when I 
tried to run the rake migrate command.

Again, still looking for hints on how I would get,  given a team, the 
matches they were in,
along with the teams for each match. And how to get info into the proper 
tables... err and how to not have to use the :through edge rails feature 
;)

Thanks again
jt

John Tsombakos wrote:

> Again, still looking for hints on how I would get,  given a team, the 
> matches they were in,
> along with the teams for each match. And how to get info into the proper 
> tables... err and how to not have to use the :through edge rails feature 
> ;)

I'm trying to figure this out.. but am not having much luck.

First thing I found out, I had to name the tables in the migration file: 
"teams", "matches", "competitions". Ok fine, the database is set up.

I was able to put Edge Rails into the Vendor directory and it does seem 
to work. So I set up 3 new models: Team, Match, and Competition, with 
the above details.

Now, using the console, so I can try to figure out how to do this, I 
tried:

t = Team.new(:number=>99, :name=>"blah")

which created a new team item. I then tried adding a match:

t.matches << Match.new(:number=>2, :winning_score=>100, :comment => 
"blahblah")

Then I saved it: t.save.

There was a new record in the Teams table, but that's it - nothing in 
Matches nor Competitions.

Do I have to create each of these items and hook up the foreign id's 
(team_id, match_id)? That seems like more work than should be necessary. 
I may as well just have one big table and duplicate all the info I 
need...

What am I missing? I really want to use Rails for this little project! 
I'm just getting more and more confused. All of the examples are very 
simple - one simple to many examples.

Help!!

Thanks,

jt

John,
Note that you used Match.new not Match.create, so you really haven't 
saved
that Match record.
If you try to get an "id" from a new record, you'll see that it's nil 
until
you actually issue a save.

Still, the join record "competition" is not automatically created when 
using
:through.
You are using :through when you are treating that join table as a 
separate
model and you'll be creating those entries explicitly, e.g. you'll need 
to
record whether a team
is a winner or not.

So you have to ways to do this:
1) Old way using HABTM
Team:
has_many :competitions
has_and_belongs_to_many :matches, :join_table => :competitions
(similar for Match)
Then you can do
t.matches << a_match, and it will create the entry in 'competitions'

2) Treat the join table as a separate model and use :through
Team:
has_many :competitions
has_many :matches, :through => :competitions
Then you create competitions which automatically establish the 
relationship
between a team and match.
You'll be setting the other fields in competition at the same time, 
since
that's the main reason you treat
that join table as a separate model ---> It's got data separate from 
just
the team/match association.
So here, you do
Competition.create(:team_id => t.id, :match_id => m.id, :winner => true)
As soon as you create a competition, the relationship between team and 
match
is established, which
is really what happens under the habtm.

Anyone have a different viewpoint on :through use?
-Bill

Bill Katz wrote:
> John,
> Note that you used Match.new not Match.create, so you really haven't 
> saved
> that Match record.
> If you try to get an "id" from a new record, you'll see that it's nil 
> until
> you actually issue a save.

Ah, thanks!

And thanks for the additional info. My next question ( ;) ) is - given a 
team number, how would I get a list of all the matches they played, 
along with the teams that were in that match?

And/Or - given a match number, how do I get the list of teams and their 
scores?

thanks again.

You need to work through the Agile Web Development book :) If you don't 
have
it, go buy it.


And thanks for the additional info. My next question ( ;) ) is - given a
> team number, how would I get a list of all the matches they played,
> along with the teams that were in that match?


team_matches = Team.find_by_number(num).matches
team_matches.each do |match|
  puts "Match #{match.number) included the following teams:"
  match.teams.each { |team| puts team.name }
end


And/Or - given a match number, how do I get the list of teams and their
> scores?


match = Match.find_by_number(num)
puts "Match #{match.number}:"
match.competitions.each do |bout|
  result = bout.winner ? "won with score of #{match.winning_score}" : 
"lost
with score of #{match.losing_score}"
  puts "Team (#{team.name}) #{result}"
end

-Bill

On 1/25/06, Bill Katz <billkatz@gmail.com> wrote:
> You need to work through the Agile Web Development book :) If you don't have
> it, go buy it.

I do have it and am reading it :)  Problem is, the book shows how to
do nice, simple relationships.. this is a bit more complex and the
book doesn't go into it - with examples.

> with score of #{ match.losing_score}"
>   puts "Team (#{team.name}) #{result}"
> end
>  -Bill

Thanks! Exactly the examples I need. It's starting to fall into place :)

I even found a mistake ;)  the next to last line should be:

puts "Team (#{bout.team.name}) #{result}"

Thanks again!!

John Tsombakos wrote:

> Thanks again!!


I'm working on getting this set up , and while I'm understanding the 
structure, I'm having a hard time getting my head around how to design 
the create/update pages - especially update.

Since using :through, I have to manage all of the foreign keys, 
especially in the connecting table - competitions in this case.  If I 
have a page to update a match, where the user can change the teams that 
are associated with that match, I'd have to keep track of what was 
changed and update all sorts of foreign keys manually. I keep hearing 
(and reading in the book) "Rails means less code", I'm thinking I'm 
overlooking something.

The more I get into this, the more complicated the updated and even 
creating is looking. I may end up having one or two simple tables and 
forego all the nice relations.

If someone has a more than simple :has_many using :through (or :habtm) 
example, where you're updating multiple thing, I'd love to see it. I've 
looked at the "tagging" type examples, with a series of checkboxes, but 
that's not quite like what I need.

Thanks all.

jt

> Since using :through, I have to manage all of the foreign keys,
> especially in the connecting table - competitions in this case.  If I
> have a page to update a match, where the user can change the teams that
> are associated with that match, I'd have to keep track of what was
> changed and update all sorts of foreign keys manually.


 You're making this sound more difficult than it is. Why not make a form
with two
textboxes for your winning & losing score, and six select drop-downs
arranged
appropriately, three under the winning & three under the losing score.
Each select has a list of all the team names
with the associated value being the team_id. Initialize each select to 
the
currently set
team for that "competition." One select tag - one competition record.

After the form is submitted, see if the teams have changed. If so, 
simply
update the
competition record corresponding to the changed select. Replace the old
team_id with
the new team_id. Save. The match_id remains constant.

Either that, or go back to the 2 table system with hardwired fields for 
the
six teams
in each match. You'll then have to write some logic to aggregate 
searches on
those
six foreign keys.