sql - How to design db for holding Dominion cards? -

i'd store information games of card game dominion. don't need know game, except that:

• there around 200 unique cards
• each game includes ten of these cards, or on occasion eleven

i'll tracking lots more each game (who played, won, etc), i'm having trouble working "supply" (the ten included cards game).

i'm thinking want 3 tables, card_name, supply, , game:

 card_name            supply                        game                                         id | name        supply | card     game | supply | player1 | player2 | ...  ----+---------   --------+------   ------+--------+---------+---------+-----   1 | village          1 | 1         301 |      1 |  'mike' |  'tina' | ...    2 | moat             1 | 3         3 | witch            1 | 200     ... | ...            ... | ...     200 | armory       

i think reasonable way represent "mike , tina played game contained village, witch, armory, , other cards didn't bother typing example". given structure (or other one, if think mine no good), i'd run queries "which games had witch , village, not moat?" is, want specify arbitrary number of "these x cards included, these y cards excluded" , search game table games satisfying criteria.

i think classic one-to-many relation, supply has multiple cards, don't understand right way search supply multiple cards.

your data structure reasonable. might suggest want game_users table well, users not listed in separate columns. particularly important if games had different numbers of users. however, aspect not relevant question.

you want solve "set-within-sets" subqueries. structure useful , supply table provides basic information needed this.

so, query appropriate "supply" records "witch", "village", , not "moat" like:

select supplyid supplies s join      cards c      on s.cardid = c.cardid group supplyid having sum(case when cardname = 'witch' 1 else 0 end) > 0 ,        sum(case when cardname = 'village' 1 else 0 end) > 0 ,        sum(case when cardname = 'moat' 1 else 0 end) = 0; 

first note changed name, id columns contain word "id" , table names in plural.

each condition in having clause representing 1 condition on cards. can tweak game information joining in games:

select g.gameid supplies s join      cards c      on s.cardid = c.cardid join      games g      on g.supplyid = s.gameid group g.gameid having sum(case when cardname = 'witch' 1 else 0 end) > 0 ,        sum(case when cardname = 'village' 1 else 0 end) > 0 ,        sum(case when cardname = 'moat' 1 else 0 end) = 0;