What's the best way to store a deck of cards in database?

I'm working on a application that needs to store a deck of hands in database. I'm not sure whats is the best way to represent this in a database.

The deck will be pre-dealt to 4 hands with each holding 13 cards by the applica开发者_运维知识库tion. After that I need to store the hands and additional data like suit distribution etc...

Is it best to create a seperate row for each hand and associate it with a deck? Or maybe it would be better to keep them in one row?

Also I'm not sure if I should keep them as text or number. Here's an example:

Sorted by suit and separated by dot
AK.32.6543.AKQ98

Or

(52 = Largest rank or largest suit or 02 = 2 of smallest suit)
405251282717161514012322200908

Any ideas?

PS: There is a reason to store the cards. We need to analyze the hand distributions, high card points etc...

I'd think the most natural thing would be to create a table for cards, that would have one record for each card in the deck, and then a record for hands, that would have one record for each card in the hand. If there's data about the hand as a whole -- the name of the player? where he's sitting at the table? how many chips he has? whatever -- then you'd need a separate table for the hand as opposed to the cards in the hand. I'd also create a lookup table for suits.

(We could debate use of synthetic keys versus natural keys here, but that's another subject.)

card (cardid, suitid, rank)
suit (suitid, suitname)
hand (handid, playername, whatever)
handcard (handcardid, handid, cardid)

My first impulse would be to make "rank" a number from 1 to 13, and translate 1 to "Ace" and 11 to 13 to "Jack", "Queen", and "King" at output time. You could store them as text, but then it would be difficult to compare ranks, if there is some need to do that. i.e. deciding if 13>10 is easy for the database; King>10, not so much.

I definitely would NOT try to cram identification of multiplpe cards into a single field, whether separated by periods or fixed length ids or whatever. Doing that would require your program to have code to take this field apart to get the data you want, instead of just letting the database retrieve the data you want, which is what databases are for.

I don't know what you want to do with these card hands, but most things you would be likely to want to do are pretty easy with a structure like I'm describing, fairly tough if you cram a list of cards into a single field. Like, "Who has the King of Spades?" Easy:

select playername
from hand
join handcard using (handid)
join card using (cardid)
join suit using (suitid)
where suit.name='Spades' and card.rank=13

Or, "How many face cards does each player have?"

select playername, count(*)
from hand
join handcard using (handid)
join card using (cardid)
where card.rank between 11 and 13

Of course "What cards does player #3 have?" is also easy:

select rank, suitname
from handcard
join card using (cardid)
join suit using (suitid)
where handid=3

Doing those queries with a packed format would require tricky string extraction calls. And string manipulation is always a pain. Like you always run into problems like, if I'm searching for 2's in text, how do I exclude the 2's that are part of '12's? etc.

It depends on the sort of analysis you'll want to do. Were it me, I'd probably default to something like (ignoring non-primary constraints)

CREATE TABLE card (
  card_id NUMBER PRIMARY KEY,
  suit    VARCHAR2(10) NOT NULL,
  value   VARCHAR2(1)  NOT NULL
);

CREATE TABLE hand (
  hand_id   NUMBER PRIMARY KEY,
  player_id NUMBER NOT NULL
);

CREATE TABLE hand_element (
  hand_element_id NUMBER PRIMARY KEY,
  hand_id         NUMBER NOT NULL,
  card_id         NUMBER NOT NULL
);

Each HAND would have 13 rows in HAND_ELEMENT. I wouldn't bother storing information about the suit distribution, I'd just compute the distribution of a hand, i.e.

SELECT suit, count(*)
  FROM hand JOIN hand_element USING (hand_id)
            JOIN card         USING (card_id)
 WHERE hand_id = :some_hand_id

I am also working on a similar project for 3 card poker. I set up my SQL like this:

CREATE TABLE cards 
(

    id int AUTO_INCREMENT,
    suite varchar(30) NOT NULL,
    rank int NOT NULL,
    dealt BOOLEAN DEFAULT false,
    PRIMARY KEY (id)

);

Then run commands to populate the database in this way:

INSERT INTO cards (suite,rank) VALUES ('spades',1);

This way you can loop through the database and change dealt to 'true' as cards are being dealt. Also makes it easy to compare ranks.