The recent DB talk in the low content thread has inspired me to think about this a little more.

Let's assume we can build a poker server from scratch and get to design stuff the way we want. We'll just focus on the question of hand histories. I haven't thought this through in detail but here are some random thoughts/snippets...

Stakeholders (aka who will have an interest in using our HHs in any way shape or form):
- The site itself (I'll also include stuff like audition etc. here)
- The players (i.e. request HHs in bulk for taxes or whatnot)
- Trackers (pretty much also the players but there may be some players with no trackers)
- HH converters and replayers
- Random tools

Common use cases (please don't be a nit about what a use case is exactly)
- Store all HHs for archiving
- Display HH in the status/chat window thingy
- Use HH for built in replayer
- Autoexport to players
- Bulk requests
- Running queries on the
- Fraud detection (bot patterns etc.)
- Checks and balances (i.e. "replay all hands and see if we have booked all the money to the right palces", audits etc)
- Datamining for the site to see most profitable games etc. pp

I'll split the stuff into two parts, feel free to contribute to either
1) Designing the HH itself
2) Database choices etc. yay

With regards to 1) I haven't really given it much thought other than
- Look at how common sites do it
- XML seems like overkill, JSON and maybe some lightweight XML like YAML or a tailor made DSL seem decent
- Human readable HHs are to be preferred
- Pokersite name can act like a namespace, handnumbers should probably be a running total but could also be running total by gametype and so forth
- There will be some general info (sitename, stakes,gametype etc.) and some game specific info (play of the hand)

2) From a turboread SQL and key-alue stores seem to be decent candidates (have to look into document stores which from the name sound about right since a HH is a document :P).

In case of a key-value store i suppose we use the HH# as the key (+site prefix or smth) so maybe we could actually encode some info in the HH# as well i.e. use a couple of bits for gametype and stuff like that

SQL isn't really bad because we'd do our design upfront but the querries may change which is kind of what SQL is good for. On the other hand it just feels a bit "odd" to press a document like a HH into SQL.

Anyways ramble on...I'll be reading up on DBs and post my thoughts once I soak up more knowledge.

First trivial insight that I had never really thought about:
- Each hand actually exists multiple times, once from each players POV (+one observer view if we want that)
- I assume this means the hand number isn't unique because each player will have a HH file with the same hand number from his POV [can easily be made into a unique thing if we just add -playername or something at the end]

Second trivial insight:
- The server will probably use an SQL database anyways for login/accounts so keep that in mind when deciding against SQL for the HHs

Third trivial insight:
- HHs actually need to be highly reliable (+low latency) because they are generated/needed for each hand played. Dunno how poker servers actually handle the case where a HH can't be generated but since it's only for "documentation" I take it it's not critical

Started reading up on Riak (the chapter in 7 databases in 7 weeks, I'm not quite finished with it yet) a bit. Since I know pretty much nothing about key-value stores the whole idea of stuff like buckets and links even existing hadn't really occured to me. I pretty much assumed it's just a DB-ized hash

Also found this which seems like a pretty usefull read so I'll share:
http://basho.com/blog/technical/2010...-introduction/

This quote from it is pretty encouraging:
Seems to fit the HH use case since the querries tend to be no real time stuff (except for sending HHs to autoimporting players but that's not really a querry but rather a write I guess)

One thing I'd look at is doing long term storage of HH on something like S3. You'd still be able to easily access them for analytics and record keeping purposes but you don't need to keep them in your primary database.

Edit: Also, there's no reason at all that you need a SQL database for login/accounts. It's straight forward functionality that any DB will handle without problems.

Edit2: I doubt that a HH is duplicated across players. I'd suspect the HH is stored with the complete information of the hand and you have separate logic that determines what portions of it any one player can see. In fact, it would be a horrible horrible idea to do anything other than this.

You're obviously right that an SQL DB isn't a given for account stuff etc. it just seems somewhat likely in my mind (mostly because I know so little about the others)

I dunno might be a security concern. I agree that it's more logical to have one HH with a masterview that knows all holecards etc. and then just have limited views

Right now I have a high level question for people that have worked with noSQL databases. Let's assume for a second we have some HH that is in JSON

What exactly are the benefits of using a document store over a key-value store where the value is simply the JSON of the HH? Seems like those are the two most interesting noSQL "variants" for the case.

Meh maybe I should finish reading the relevant chapters of the book. FWIW they included postgres (SQL),riak,redis (both key-value),mongodb,couchdb (both document stores), neo4j (graph based..gonna ignore that for now) and hbase (column oriented...gonna ignore for now)

I guess my noSQL comparison for this case will be between riak,redis,couchdb and mongodb. Hopefully after reading up on all I'll also have a decent oerview of the paradigms

Redis will probably be a 100% given and would be used in unison with a primary persistent db.

Also it's a really good idea to try and simplify your stack as much as possible while still balancing acceptable performance and good development time. Less moving parts and less context switching really helps in every aspect of launching something into production.

I also wouldn't throw out neo4j at all. A graph database is an ideal solution when you need to get information out of highly connected data. Will it fit perfectly into your poker web server idea? Maybe, maybe not. At least research it because for the correct use cases a graph db can make your life A LOT easier.

A few easy examples:

A social network where you're tracking friends and those friends have friends and now you want to find friends of friends, or even friends of n+1 (possibly recursive to infinity).

Finding the shortest path between 2 actors. Think of something like the Kevin Bacon game.

I wouldn't try to prematurely optimise the size of the DB like this already, split it into multiple columns. Combining things like that will be a huge headache and will make searching quite slow

For fun I designed a quick one as to how I would do this with a relational database:



There's probably some stuff missing (such as specifying winners so that it works with split pots) but that would be my start. Using Linq in ASP.net I would be able to do some very flexible queries I think.

There's also a lot of opportunity for improving performance with denormalisation by adding extra columns on each tier. I'm confident if you wrote everything nicely the insert performance would definitely be good enough for dozens of real time tables, don't really know about bulk inserting though.

I'm a bit late to the party here but you should consider CouchDB. The K:V stores are fantastic for real-time data, I'm a huge Riak fan and love it's eventually consistent approach, but the read speeds of CouchDB are really fantastic. It also has bi-directional replication which seems perfect for a poker application. Realistically you could give every player their own unique database and exports would be a breeze.

CouchDB also lets you write pre-defined views that it indexes and caches the results. The only knock on Couch is that essentially every "query" is a view so you need to know in advance what data people are looking for. I could be ignorant on this, but how many different queries would a poker app have?

Couch also uses JSON with a RESTful HTTP interface.

Yeah that does indeed sound good. Riak's "just plug in a new process if one fails" approach seems pretty fantastic. I suspect Couch might have something similar because that just seems to be Erlang doing it's thing

does it make sense to have something like "DealerAction" instead of some of the information you are storing in the Hand table? A DealerAction would be dealing a card (to player or to board) or perhaps awarding a pot. Then to reproduce all of the events in a hand you can select all PlayerActions union all DealerActions for the given HandId, ordered by time (or add a custom field to indicate the order in which events happen).

Main thing that made me think this was "How would you extend this design to games that are not hold'em" - the Hand table would look a bit wrong.

I think it all depends on the use cases - you'd use different design/architecture/db for different use cases and not have one solution to fit all.

Hand histories for display inside the client can be stored in memory or on (client) hard disk.

Complete HH transcripts can be stored in a key/value store for fast retrieval.

Summarised/aggregated hand data (based on the complete transcripts) can be stored in a traditional RDBMS for fraud detection, win/loss and other general querying.

You could also store all the atomic events that make up a single poker hand, allowing you to replay and analyse pretty much anything (although not in real time) - maybe hadoop/pig would be suitable for something like this?

If you really wanted just one solution then I would stick with a RDBMS because it's the best all rounder - store the raw HHs as compressed BLOBs and use summary tables for other queries.

Schema design again would depend on the use cases and as RoundTower says, you'd need to think about how to deal with all types of poker games...

Talking table-wise, I wouldn't embed anything unique to the game type in the hand history table.

I would create a separate table say;

hand_community_cards
( hand_id number,
card_type_id number,
card_value whatever
)

...which stores the community cards. Then you can have another table like;

gametype_community_card
( gametype_id,
card_type_id
.
.
)

.. which then makes it future proof for other games. (Draw games with no flops, games with two flops, games with an Ocean, etc)

Interesting thread, because as a programmer myself, the clumsiness of almost every poker site in handling their data tilts the hell out me. Stars is great now where can request whatever hands you want and they zip it all up and give you a unique download link for it.

In the past however, where I've had disconnections from Euro sites and requested hands it's been a joke. Prima is a nightmare, with their game history search screen in the poker client. Just give me the fracking text you store in the .DAT file, not a little play by play image in the client...!

Anyway, I've often thought poker in general would benefit from an agreed on standard for hand histories. Would be great if there was something like the W3C for poker. I can only dream of a day where hand histories are shared in a common internationally recognised format like chess games are basically with PGN files....

Another point - I don't think the hand history IDs should be sequential numbers. It should be some cryptographic hash so that any game provider can prove any given hand history a) was actually generated from their system, and b) matches their version of events.

Or even if the HM/PT of the future could verify hands are true themselves. There must already be some sharing/connection between them because I think HM hooks into the Blitz.dll Stars brought out for Zoom.

CouchDB is a great choice. I'd seriously look into S3, though, as others said.

Keep in mind different games will not have flop1, flop2, flop3, turn, river

That is why a document DB is so good for this.

You can just have something like "steps", and then inside of steps would be flop, turn and river for holdem and whatever else for any other game.

It would be perfectly fine to have that and then also have a hand with completely different steps for a different game type.