Relational database structure for Texas Hold'em poker data

Question

Hello,

I am planning a relational database to store poker game data (like what would be included in the hand histories). I would like help figuring out how to design the associations. It seems like there should be 4 models: Game, Hand, Player, and Action (single action of a given player, like raise, fold, call). Let me lay out what I have:

class Game < ActiveRecord::Base
  has_many :hands
  has_many :actions
  has_and_belongs_to_many :players
end

class Hand < ActiveRecord::Base
  has_many :actions
  belongs_to :game
  has_and_belongs_to_many :players
end

class Action < ActiveRecord::Base
  belongs_to :game
  belongs_to :hand
  belongs_to :player
end

class Player < ActiveRecord::Base
  has_and_belongs_to_many :games
  has_and_belongs_to_many :hands
  has_many :actions
end

Does this make sense?

Answer 1

Makes sense as a first draft. Associations arising from what you have give the following tables and keys:

Game       :: Game_id (PK);....
Hand       :: Hand_id (PK); Game_id (FK);....
Player     :: Player_id (PK); Action_id (FK);
ActionType :: ActionType_id (PK); Type;
(Note this table will only have three records - raise, fold, call)
Action     :: Action_id (PK); ActionType_id (FK); Game_id (FK); Hand_id (FK); Player_id (FK);....
PlayerHand :: Player_id (FK); Hand_id (FK); Has_or_Belongs; (PK is (Player_id, Hand_id))
GamePlayer :: Game_id (FK); Player_id (FK); Has_or_Belongs; (PK is (Game_id, Player_id))

Answer 2

If you're planning to use has_and_belongs_to_many, you should probably switch to using has_many ..., :through as it's much easier to manage. You already have an Action model that does what you need without having to create some join tables:

class Game < ActiveRecord::Base
  has_many :hands
end

class Hand < ActiveRecord::Base
  has_many :actions
  belongs_to :game

  has_many :players,
    :through => :actions,
    :source => :player
end

class Action < ActiveRecord::Base
  belongs_to :game
  belongs_to :hand
  belongs_to :player
end

class Player < ActiveRecord::Base
  has_many :actions
  has_many :played_games,
    :through => :actions,
    :as => :game
  has_many :played_hands,
    :through => :actions,
    :as => :hand
end

Generally, the fewer tables you have involved in your queries, the faster they will run. Involving any kind of JOIN is going to lead to unpredictable query performance.

Be sure to index your tables carefully, and use the EXAMINE statement to ensure you're hitting indexes when using them. Table scans will be extremely painful if you load this up with millions of records, and that doesn't take long in games like this as a single hand involves dozens of actions and it's typical to play dozens of hands every hour.