Poker Hand Tracking Database Structure

[robbeh]

I have been tasked with a tough DB design as far as I can tell, I hope I am wrong.

I need to create a database that stores hand history for a poker player who plays holdem. Here is an example of a 1 hand on a small scale: (the **items** are just for description purpose)

**9 Players sit down at a table**
Player 1
Player 2...Player 9
**Hole cards are dealt**


Your hole cards (2 cards)
Player 1 acts (can call, raise, fold)
Player 2 acts (can call, raise, fold)...Player 9(can call, raise, fold).
**Potential for more rounds of betting if someone raises**
**Flop is dealt (3 face up cards) after all players call/fold**
Flopped cards
**another round of betting**
...so forth

So as you can see there are a lot of actions to track. The issue is, if someone folds, they are no longer in subsequent rounds of betting but obviously there will be cases when no none folds.

I am just trying to wrap my head around how I can create the database design.

Is each hand an entry to a table? - this would have 100's of fields to cover all possible actions
Do I store each player in their own table, give each hand a unique hand ID and store the players actions for each hand ID and round of betting?

I am open to any and all ideas here.

Thanks,
Robb

[June7]

Not a field for each action, a record.

[robbeh]

Not a field for each action, a record.

But then how would I query that? My understanding is a record is a row in a table comprised of fields. (just to make sure I am on the same page). So you would suggest each table is a hand? and each record of that table is an action of that hand? Example

Table: Hand 1
Player 1
Player 2
...
Player 9
Big blind
Small blind
Your 2 cards
Player 1 Bets
Player 2 Folds
...
Player 9 Calls
3 Flop cards Dealt
Player 1 Bets
Player 9 Calls
etc...

The issue then is, sometimes there might be multiple bets in a round of betting, so all the tables will look different and I feel like a query would be really tough.

The end goal is to be able to say (query), show me all hands where Player 1 bet after the flop and player 8 raised.

Thanks so much for your help, I am really stumped on this one.

[June7]

Not a table for each hand. HandID would be a field in table. Something like:

tblPlayers
PlayerID (PK)
LastName
FirstName

tblGame
GameID (PK)
GameDate
Dealer

tblGamePlayers
ID (PK)
GameID (FK)
PlayerID (FK)

tblHands
HandID (PK)
GameID (FK)

tblHandDetails
HandID (FK)
PlayerID (FK)
ActionType
ActionDescription

Sorry, it does get a little fuzzy for me about how to structure tblHandDetails.

[robbeh]

Aha ok I see what you are saying. Yeah the tough part is the hand details/actions.

Basically each record of the tblHandDetails is an action a player did related to a HandID?

Example:
HandID: 1234
PlayerID: Robbeh
Position: Player 1 (there are names for each position on a table so I would probably use those)
Round of Hand: Pre-Flop
Round of Betting: 1
ActionType: Bet
Amount: $10

I have realized to deal with the issue that there might be multiple bets in a round of betting I can either add a ROund of Hand (so I know what part of the hand we are in) and Round of Betting to handle if one player raises, then the other player raises over top etc.

So barring the logic does work I have one last question.

There are 30 000 hands recorded. I know 30 000 is small amoutn of data BUT if we assume each hand has roughly 10 actions....that's 300 000 records for the tblHandInfo...what becomes too big or where will I see the speed of my database slow down?

I feel like I am so close now!

[June7]

Access has 2GB size limit. How much memory will 300,000 records use - depends on how much data in each record. Create 300,000 records and find out.

Yes, complex queries, forms, reports can perform slower with large datasets, especially if data is passing over a network. Speed of network is also a factor.

[orange]

I don't think that database size will be a big issue. I think getting the database structured to record the data in a format to answer your questions --that is, meet the requirements, is the key.

You might want to build a "flowchart" of the steps and possible actions involved to help sort out exactly what data should be recorded and when it gets recorded. This chart would evolve from overview to most detailed with time. As you build your chart of the processes and actions, you will also evolve data model of what data is available/recorded.

Set up some test scenarios with various hands and actios etc and work them with the evolving data model. Pose questions based on the kind of info you want for analysis and make sure the data is available. Repeat this and adjust the model until it satisfies your needs at the model stage. Then, you'll know exactly what tables and relationships you will need - and from there, build the database.

Good luck. Interesting project.

[robbeh]

Thanks Orange (and June),

This has been very helpful and I am going to do what both recommend. With the initial guidance of June for the basic foundation and the flow chart idea I think I can get a good start.

Orange, you bring up a good point about recording the data. Taking the data from the txt file to the database and storing it in the right tables is going to be the toughest part. It's in a nice CSV (well delimited by hard returns) but as we see the data isn't very linear and is very situational.

Would I be right to assume the best method would be using VBA and run through a bunch of loops with conditions and SQL Inserts to get the data into the right tables?

I'll keep you guys posted on the project as I am sure I'll want a second set of eyes once it's created to polish where ever I can.

Cheers,
Robb

[rpeare]

I would just add one more thing to this instead of storing a text value for some of these fields store foreign keys to other tables to there is no variation in your data due to bad spelling or shorthand and secondly, make sure your data has a sequence (don't rely on the autonumber of the tblHandDetails table) it's fairly easy to insert a sequence number and reorder the table around moving the 'sort order' for lack of a better term.

so you'd have tables

tblPosition
Pos_ID Pos_name
1 1st Chair
2 2nd Chair

tblHandRound
HR_ID HR_Desc
1 Pre-flop
2 Flop
3 Turn
4 River

tblActionType
Act_ID Act_Desc
1 Bet
2 Raise
3 Fold
4 Check

then your data entry is forced to have the correct data entry
p.s. let me emphasize you have a sequence of events in your table and do not rely on the autonumber, otherwise you may end up with garbage particularly if the betting goes around the table more than once.