Using a table purely for its function as an index to facilitate searches

I am writing an application to carry out analysis on online poker hands. I represent a playing card with a number 1-52. I do this in a way which allows me easily to extract the suit and denomination of the card.

I am writing in Java and am using MySQL as t开发者_运维问答he database.

An example of a problem I am facing is described below.

I will be holding over a million individual poker hands, and each hand will have up to ten players each of which will start with two cards at the beginning of the hand.

So just in terms of starting cards alone there are possibly 20 million values stored. I will need to do things like:

• Identify every hand in which one of the players is holding a specific card (say seven of hearts)
• Identify every hand in which the two cards a player is holding are part of a particular set (e.g suited connectors, suited cards, at least one ace etc).

I think the right way to do this is follows:

1. I will have a table (Table A) which stores the details of a players details in a game. One row per player per hand.
2. To define the two hole cards for a given player, I will have a one to many relationship with Table A to a table (Table B) which is structured as follows:
   • Have a foreign key index into Table A to define the row corresponding to a given hand and player
   • Have two rows in Table B for each row in Table A, one for each hole card.
   • This way I can search Table B for the cards I am looking for and then use the relationship to find the players and games in which the cards occurred.

So this seems right, but I am effectively using Table B just as an index to facilitate the search.

The alternative would be to store the starting cards directly in Table A, as two fields, Card 1 and Card 2, each of which would be integers. The big problem with this is that searching would be much more complicated as I would always have to check both cards specifically as distinct fields. Also bear in mind that the hole cards are just one place where I need to store cards. In practice there are many other cards in a game and I need to store and search for all of those. This is why I feel my approach is probably right as it is properly normalised.

Are there any drawbacks with my approach?

An enum can seem very valid here -- the data basically never changes, unless you also intend to play Tarot (has a Cavalier between J and Q) or Rummy (adds 2-3 jokers per deck).

That said, for cards I'd personally stick to an int because you can use it to introduce sorting and where-condition magic. For instance, if you have:

51 - AS
50 - AH
49 - AD
48 - AC
47 - KS
...
01 - 2D
00 - 2C

Than it is safe to say that (i mod 4) yields the card's suit (3 = S, 2 = H, etc.), and thus suit rank, and that i - (i mod 4) is in relation with the card number and thus card rank. A few joins and (possibly functional) indexes will let you extract stats in no time.

The point here is, internally, an enum is the same as having a 52-row table; it's just that a) you don't see the actual values of the numbers, b) they're pre-evaluated internally before actual queries occur (which you should be doing anyway) and c) you can't introduce numbering magic which might otherwise be useful for card games.

My background is datawarehouse design, but this seems to lend itself well to storing data in a bit structure, 52 bits long - or maybe 54 bits if you include two jokers that other games may employ.

This would be a generalised structure that would enable any number of starting cards to be stored, or even a state as the game progresses as new cards are dealt!

You just need to decide your e.g. your 54 bit structure would be '1000100000....(lots of bits)....0000' if a player had Ace of Clubs and the 5 of Clubs, then you could use bitmaps to select rows from the database that match your criteria. If you did some clever grouping of the suits, then you could get some very quick analyses!

The bit structure is especially important if space is an issue.

Sounds like a fun project!