Looking for DB/Model Design Suggestions

[johnt519]

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!

[johnt519]

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

[johnt519]

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)

[johnt519]

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

[johnt519]

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

[johnt519]

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

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

[johnt519]

what does this do ?

:through => :competitions

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

thanks
adam

[johnt519]

Bill K. 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

[johnt519]

Bill K. 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

[johnt519]

John T. 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

[johnt519]

Bill K. 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.

[johnt519]

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

[johnt519]

On 1/25/06, Bill K. [email protected] wrote:

You need to work through the Agile Web D. 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!!

[johnt519]

You need to work through the Agile Web D. 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

[johnt519]

John T. 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

[johnt519]

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.