SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict JNKlein's Avatar
    Join Date
    Sep 2004
    Location
    New York, NY
    Posts
    258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    [RoR] db schema suggestions needed

    Code:
    # 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?

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    1. Create a team/roster with a single player?

  3. #3
    SitePoint Addict JNKlein's Avatar
    Join Date
    Sep 2004
    Location
    New York, NY
    Posts
    258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah - I considered that. Thanks for bringing it up. That seems kind of hack-ish also, but maybe the best solution.

  4. #4
    Mal Reynolds Mandibal's Avatar
    Join Date
    Aug 2003
    Location
    Columbus
    Posts
    718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by JNKlein
    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.
    Erh

  5. #5
    SitePoint Addict JNKlein's Avatar
    Join Date
    Sep 2004
    Location
    New York, NY
    Posts
    258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •