[RoR] db schema suggestions needed

[JNKlein]

# players
class Player < ActiveRecord::Base
  has_many :rosters
  has_many :teams, :through => :rosters
end

# teams
class Team < ActiveRecord::Base
  has_many :rosters
  has_many :players, :through => :rosters
end

# the relationship between players and teams
class Roster < ActiveRecord::Base
  belongs_to :player
  belongs_to :team
end

# a line item account of a players performance in a game
class Stat < ActiveRecord::Base
  belongs_to :player
  belongs_to :game
end

# games
class Game < ActiveRecord::Base
  belongs_to :hometeam, :class_name => 'Team', :foreign_key => 'hometeam_id'
  belongs_to :awayteam, :class_name => 'Team', :foreign_key => 'awayteam_id'
  has_many :stats
  has_many :players, :through => :stats
end

The above should be pretty self explanatory (gosh rails is great!), but to make sure: I have players playing games as members of a team, and I am keeping statistics for those players.

I have two issues:

1) I want to also add support for games where players play eachother one on one, but want these to exist as game objects because they ... well... are. An "is_teamgame?" with potentially null "player1_id" and "player2_id" seems totally unelegant. Is there a better way?

2) I want to add support for other games with different kinds of stats, yet also store those as "games". My current stat model corresponds to a table that just has the kind of statistic as a column, and the rows connect a player and the game he was playing in with all of those stats. Because I account for things like averages, it doesn't seem like I can just populate the table with more columns for more stats and null them out depending on the game type, since that would ruin my ability to, for instance, count how many games of a particular type a player has been in. How can I account for this?

I prefer simple, elegant solutions. It seems like this is the right place for some sort of table inheritance, but I'm not really sure how that works. Are there any resources on this info?

[vgarcia]

1. Create a team/roster with a single player?

[JNKlein]

Yeah - I considered that. Thanks for bringing it up. That seems kind of hack-ish also, but maybe the best solution.

[Mandibal]

Yeah - I considered that. Thanks for bringing it up. That seems kind of hack-ish also, but maybe the best solution.

I don't think it's hackish at all. You just need to readjust your thinking on what a team is. In this case a team is just one player. The other team is just one player. Your team table doesn't even have to know this though because it only cares about rosters even in the case of the players association. Does the team table really care if there are 10 players or 1? Really you'll just be creating a new single player roster and associating that to a team. Doing it this way allows you to continue to use all the same code your using for multi-player team games for single-player games because your just going with the assumption that a roster can be any size.

For the stats problem what I've done is create different stats tables. There are two ways though to go about having different stats.

1) Multiple tables with each column being the the stat category.
2) A pivoted table. This table would store all stats in one table by making the columns generic. ie the columns would be game_id, player_id, game_type(or stat category/type), stat_name(unless this is taken care of by category) and then stat

I am currently using #1. My site is for the NCAA Football video game. So my stat tables are passing, rushing, receiving, blocking, defensive, kicking and so on. The passing table has columns for rating, attempts, completions, yards, TDs, INT's etc. I actually put the averages as methods in the models for each statistical category. I call the passing, rushing etc the categories. I do use STI for each one because there are game stats, season stats and career stats. I could have just stored game stats and calculated the other two each time they were viewed but I decided to store those as well and only calculate them when the game stats were updated. To get the stats I just use Object.const_get(category_name.classify) to generate the model for the stat category. I also went ahead and created associations for each category in the game, season and player models.

The second just allows you to store everything in one table because it does not tie the table to a specific stat or type. I didn't use this type for my site described above but I do use it for a distance education site I develop at work. I use it to store all fields submitted for assignments. So any form will fit into it and any changes to forms don't have a major impact on the code used to display, process the form submissions. I can see it being useful for a site that needs to collect stats but in this case I already had the database setup from a previous non-rails version and decided to stick with the legacy database structure.

[JNKlein]

Thank you, Mandibal. My concern was more with the view logic being hackish because of these changes, but the more I think about it, the less I care I like the ideas you are using.