Poker flops database help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Poker flops database help

Author

Topic

thorntoc
Starting Member

1 Post

Posted - 2009-12-17 : 11:18:28

Hi all,
I've created a database of all possible flops(3 card combos) from a 52 card deck. It contains one table which consists of FlopID, FirstCardRank, FirstCardSuit, SecondCardRank, SecondCardSuit, ThirdCardRank and ThirdCardSuit.

What I want to do is create a query to find flops that don't contain certain cards, presumably the best way to do this is to use derived tables? So for example I want to find out how many flops contain a 6 if I exclude 6 spades and 6 diamonds.
Also I think it may be better to have 2 tables, 1 for the deck of cards with CardID etc. and one for the flops with FlopID and CardId for the 3 cards.
Any help would be much appreciated

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-17 : 12:54:14

This would be my first stab at a design (and an example query)...

declare @FlopCards table (FlopSetId int, CardPosition tinyint, CardRank char(1), CardSuit char(1)
    primary key (FlopSetId, CardPosition))
insert @FlopCards
          select 1, 1, '4', 'H'
union all select 1, 2, '5', 'H'
union all select 1, 3, '6', 'H'
union all select 2, 1, 'T', 'H'
union all select 2, 2, 'J', 'H'
union all select 2, 3, 'Q', 'H'
union all select 3, 1, 'T', 'H'
union all select 3, 2, 'J', 'H'
union all select 3, 3, '6', 'S'
union all select 4, 1, '6', 'H'
union all select 4, 2, '6', 'D'
union all select 4, 3, '6', 'S'

select count(FlopSetId) as Count
from @FlopCards
where CardRank = '6'
  and FlopSetId not in (select FlopSetId from @FlopCards where CardRank + CardSuit in ('6S', '6D'))

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-17 : 13:35:47

Soon will come his requirement for the query to be dynamic, with any combination of suits and values as parameters.

Jim

Everyday I learn something that somebody else already knew

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-17 : 13:56:52

One step at a time

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-18 : 05:13:52

quote:

This would be my first stab at a design (and an example query)...

declare @FlopCards table (FlopSetId int, CardPosition tinyint, CardRank char(1), CardSuit char(1)
primary key (FlopSetId, CardPosition))
insert @FlopCards
select 1, 1, '4', 'H'
union all select 1, 2, '5', 'H'
union all select 1, 3, '6', 'H'
union all select 2, 1, 'T', 'H'
union all select 2, 2, 'J', 'H'
union all select 2, 3, 'Q', 'H'
union all select 3, 1, 'T', 'H'
union all select 3, 2, 'J', 'H'
union all select 3, 3, '6', 'S'
union all select 4, 1, '6', 'H'
union all select 4, 2, '6', 'D'
union all select 4, 3, '6', 'S'

select count(FlopSetId) as Count
from @FlopCards
where CardRank = '6'
and FlopSetId not in (select FlopSetId from @FlopCards where CardRank + CardSuit in ('6S', '6D'))

Why not normalise?

With an card table (and a suit table if you want to go the whole hog)

You could also add a rank column to the card table to indicate the value of the card. Would make determining winning hands easier.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-18 : 05:39:57

Which normal form is it breaking?

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-18 : 06:33:36

well you are duplicating 2 articles of information about each card where you could get away with only 1 tinyint link. If you wanted to add more information about each card then you'd have to add much more information to the table @flopCards rather than just adding another column to a @cards table.

instead of storing cardRank and CardSuit in @FlopCards why not store a cardID. Both cardSuit and cardRank are attributes of card.

My set theory is a little rusty but I don't think you are conforming to any normal form at the moment.

you aren't in first normal form as you have repeating groups that are not contrained CardRank char(1), CardSuit char(1) -- if that was a foreign key to a card table then this would obey at least first normal form.

I don't think you have 3rd normal form because your cardSuit and CardRank aren't dependant on the key.

to be honest though -- my set theory is rusty and I'm not calling your design a bad one, I'm just asking why not have the card table?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-18 : 07:16:38

I think you're getting confused.

If any data about cards needs to be stored, a card table can be added with CardRank and CardSuit as the primary key.

I would be comfortable with a design with an arbitrary CardId also.

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2009-12-19 : 12:30:28

I might do something like this...

create table dbo.cardNumber 
(  
	cardNumber tinyint  identity(1,1),
	cardDesc varchar(10)
)
go

--insert card numbers
insert cardNumber
select 'Ace' union all 
select '2' union all 
select '3' union all 
select '4' union all  
select '5' union all 
select '6' union all
select '7' union all 
select '8' union all 
select '9' union all 
select '10' union all  
select 'Jack' union all 
select 'Queen' union all
select 'King' 
go

create table dbo.suit
(
	suitID tinyint identity(1,1),
	suitDesc varchar(10)
)
go
--insert suits
insert suit
select 'Clubs' union all
select 'Diamonds' union all
select 'Hearts' union all
select 'Spades'
go

create table dbo.deck
(

	cardID tinyint identity(1,1),
	cardNumber tinyint,
	suitID tinyint
)
go

--build deck
insert deck
select cardNumber, suitID from cardNumber cross join suit
go

--card array to exclude
create table dbo.cardExclude
( 
	cardID tinyint 
)
go

--cardID's for 6S and 6D
insert cardExclude
select d.cardID --, c.cardDesc, s.suitDesc 
from deck d
left join cardNumber c on c.cardNumber = d.cardNumber
left join suit s on s.suitID = d.suitID
where c.cardNumber = 6
and s.suitDesc in ('Spades', 'Diamonds')
go


--card array for cards to include
create table dbo.cardInclude
( cardID tinyint )
go

--cardIDs for 6's (but not the excluded ones)
insert cardInclude
select d.cardID --, c.cardDesc, s.suitDesc 
from deck d
left join cardNumber c on c.cardNumber = d.cardNumber
left join suit s on s.suitID = d.suitID
where c.cardDesc = '6'
and d.cardID not in (select cardID from cardExclude)
go

--here is what we have so far
select * from deck
select * from cardNumber
select * from suit
--card exlusions and desired card(s) to inculde
select cardID from cardExclude
select cardID from cardInclude

--here is a full deck of cards
select d.cardID, c.cardDesc, s.suitDesc 
from deck d
left join cardNumber c on c.cardNumber = d.cardNumber
left join suit s on s.suitID = d.suitID

--here is the result for a 3 card hand with applied ruleset
select hand.*
from
	(
	select d1.cardID card1, d2.cardID card2, d3.cardID card3
	from deck d1, deck d2, deck d3
	where d2.cardID > d1.cardID and d3.cardID > d2.cardID
	and 
		(d1.cardID not in (select cardID from cardExclude)
			and d2.cardID not in (select cardID from cardExclude)
				and d3.cardID not in (select cardID from cardExclude)
		)
	) hand
where 
	hand.card1 in (select cardID from cardInclude)
	or hand.card2 in (select cardID from cardInclude)
	or hand.card3 in (select cardID from cardInclude)
order by 
	card1,card2,card3

--clean up
drop table suit, cardnumber, deck, cardExclude, cardInclude

WARNING: I am an old rusty dog and this is unknown to be accurate... :)

Best wishes to you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources