I have a project which is for a databse to hold data on a poker room

So there are pokertables , tournaments which are made up of multiple tables,
and hands.......just this for now as this is the bit i am stuck on.

As Tournaments are made up of pokertables i have a Table called TourDetail
which loks like this:

TourDetail
TourDetailId
TournamentId
TableId

So that tabe stores all the tabls in a tournament.

However in a normal non tourney style game i just go to my PokerTable table

PokerTable
TableId
Name
BuyIn
etc....

Should i make a new db table called NonTourTable or something and do this
NonTourTable
NonTourId
TableId

Then i have separated the tournament tables from the non tournament?

I dont want to do this because when a tournament starts i need to create the
tables required for it everytime. so if the tournament requires 100 tables
and i run 5 tournaments a day thats 500 inserts a day into the PokerTable
table and the ToureDetail table? Is that ludicrous or acceptable? Is there a
much better way?

Daniel wrote:
Show quoteHide quote500 inserts per second could be significant. 500 per day is nothing to
get excited about.

I know nothing about Poker tournaments so I won't try to second-guess
your logical model. I will only say that you should start with a
conceptual model of the reality you are representing and then use
design principles like Normalization to guide your logical design. The
time to take account of any performance and maintenance issues is after
you have got the basic design straight.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.  It is very hard to debug code when you do not let us
see it.

and hands.......just this for now as this is the bit i am stuck on. <<

But no table of tournaments, no table of players,  no hands, no rounds
of play, etc.

I could have sworn that the poker tables were physical locations for
players and hands played.  Please post the DDL and not your vague,
personal narrative.

Why is that kind of Poker logically different?  Why did the logical
nature of the Poker Table change?

I am pretty usre that there is.  But the specs are so bad, nobody can
understand the problem.  Why are you recording non-tournament games?
Etc.

Hi thanks for your help.

Sorry Celko  but i cannot post the commercial full database design, its very
complex and took a lot of work to put together. Otherwise i would.. A lot
can be inferred, i have helped many ppl and never needed the full design
model. As i said there are the only relevant tables for this problem. If you
think others would be needed to solve it then just say 'presuming you had a
table such as' . Also my Id fields let you know of other tables,
TournamentId obviously goes to a Tournament table and that obviously holds
basic tournament details etc etc

The reason i am splitting the logical situation, i.e. one table holding
pokertable data, is because when you take tournaments into account you make
a table such as TourDetail and put in a column for the TournamentId and the
TableId it is associated with. Thus a list of all tables used for that
tournament.

Sounds fine as you can get the table information from that tournament table
when you need it.

So now take this scenario.

Imagine your loading the lobby, you need to display all tha tables
available. However if the table is a tournament table it must go in a sub
category in the lobby under TournamentId not the individual tables. the only
sql i can come up ith to return that data is

select * from pokertable as p, tourdetail as td where not
p.tableid=td.tableid

Thus returning all the tables not being used in any tournament

Then for the tournament ones i do the same but inverted

select * from pokertable as p, tourdetail as td where p.tableid=td.tableid

Maybe that's fine to do it that way? I would rather do it in one sql
statement. My other option was to add a TableType field to PokerTable and
that TableTypeId would tell me if it was a tournament table or not. Just
felt like data redundancy as the tourdetail table would be telling me this
information anyway.

I do tend to over complicate things sometimes in order to avoid some slight
ineffiency. I am presuming i have the best way already then and the 2 sql
statements isn't actually a big deal?


Show quoteHide quote

So we have a "Tournament" table... You did not mention that one before.

So the only purpose of the "TourDetail" table is to store relationship
between poker table and tournament and nothing else?


But this schema allows the situation when one single poker table is assigned
to multiple tournaments and it does not prevent a poker table being in
"PokerTable" table and "NonTourTable" at the same time. You'd have to
enforce those constraints in the client application and this solution
requires moving records between "PokerTable" and "NonTourTable".


Show quoteHide quoteI'm not sure if I understand your model correctly, but if I do, then
I'd do it like this:

"Tournament" table fields:
TournamentID is primary key and can't be null,
some other fields detailing the tournament info

"PokerTable" table fields:
PokerTableID is primary key and can't be null,
TournamentID is foreign key (to Tournament.TournamentID) and CAN be null,
some other fields (like "Name", "BuyIn" etc.) detailing the table info.

No other tables involved.
When one poker table is used in a tournament, then the tournament ID is
placed in the poker table data. When the poker table is not used
in any tournament, then the "TournamentID" field of "PokerTable" table
is set to null.
This way poker table can't be assigned to more than one tournament
and can't be assigned to any tournament and be a non-tournament table
at the same time. Reasigning (or unasigning) table means only one single
update on the poker table record in "PokerTable".

The query to list a non-tournament poker tables would be:

SELECT * FROM PokerTables WHERE TournamentID IS NULL

The query to list poker tables that are assigned to tournaments would be:

SELECT * FROM PokerTables WHERE TournamentID IS NOT NULL



Kamil 'Hilarion' Nowicki

Hi Hilarion

I know i didnt mention a Tournament table. The point i was making to the
other guy is if someone shows me this:

TourDetail
TourDetailId
TournamentId

I will presume the person designed the database logically and you can
clearly infer that TournamentId is a primary key of a Tournament table. I
will be more precise in future for people who cannot figure or intelligently
guess at that.

I like you point, your way i can remove the intermediary TourDetail table
all together.

However what if i start one tournament and update the table tournamentid
field to the number of that tournament then days later start another
tournament and reuse that table, so thet row gets updated to the tournament
id of this new tournament. So the same table has been used on two
tournaments. Just not at the same time.

I am presumeing you are saying at the point of creating a poker table i will
ALWAYS insert a new row and if the new table is to be created for use in a
tournament then the tournamentid field will be not null and so on.

If that is right then yes i think i will go with that option. Thanks.


Show quoteHide quote

The problem is that in many cases it's not that simple. People posting
here (and in other newsgroups of forums) represent very different
levels of knowledge, very different approaches to field names and
making such assumptions leads to misunderstandings many times.


The problem is that more intelligent people (or rather people which
have experience in communicating with other SQL "architects") know
that it may not be that simple.


The other thing is that even if one column is intendet do be
a kind of foreign key, it's still not obvious if there is any
foreign key constraint defined and how. The relation may
be based on that column (eg "TournamentId") in many ways.


Yes. (It's a bit confusing to use word "table" here, because it can
mean "poker table" or "db table".) The schema I wrote about stores
only current data on how tables are assigned to tournaments.

It wasn't exactly my point. If you want to hold historical data (not only
current status of poker tables assignment to current tournaments), then
the schema I suggested may not be so good.

The way I like to design schemas is to make it as close to reality
as it's possible and then (if it simplifies any tasks or boosts
performance) modify it to suit my needs. In this case I'd try to
hold to the solution when one poker table is always represented by one
record in one DB table, and unless that poker table gets physicaly removed,
the record remains where it is (it may change it's state, which means
some updates to the record). Same goes with tournaments and poker
table assignments.
The requirement of tracing not only current state, but also previous
(or future) states makes it a bit more complicated. If the tournament
data is static (when you enter it into the DB then it remains unchanged
or at least the changes do not have to be traced/audited), then
I'd leave it as a single table (and single record reflecting one
tournament). I'd also try to leave poker tables in a similar way,
which means that "PokerTable" DB table would store only the data
that does not change in time (or does not have to be traced in
time, like poker table number, poker table name, poker table price,
poker table time of instalation, etc.). The poker table to tournament
asociation would land in separate table (something like your "TourDetail"
DB table). If the poker table is always assigned to a tournament
for the whole duration of the tournament, then probably this
"TourDetail" DB table should only store TableId, TournamentId and
the data that is specific for the use of this poker table in
this tournament (I do not know what that may be). If the poker
table can be unasigned in the middle of tournament (or get assigned
to it in the middle of tournament), then you'd have to include
the time span info of the assignment into the "TourDetail" DB table.
In this case the select statements to get poker tables which
are currently (or at any other point in time) assigned or are not
currently (as before) assigned you'd have to use:


In the first scenario (poker tables are
always assigned to tournaments for whole tournament duration):

SELECT pt.*, td.*, t.*
FROM PokerTable AS pt
LEFT OUTER JOIN TourDetail AS td
ON (pt.TableId = td.TableId)
LEFT OUTER JOIN Tournament AS t
ON (td.TournamentId = t.TournamentId)
AND (GetDate() BETWEEN t.DurationFrom AND t.DurationTill)
WHERE td.TournamentId IS NOT NULL -- unassigned
-- or
WHERE td.TournamentId IS NULL -- assigned

In the query above you can also get all the tournament
data including those related to the poker table itself.
If you want to get the assignment state in another
moment in time (eg. to check what tables will be
unassigned tomorrow) you'd have to replace "GetDate()"
with @check_date (or something like that).

In the second scenario (poker tables may get assigned/
reassigned/unassigned during tournament duration):

SELECT pt.*, td.*, t.*
FROM PokerTable AS pt
LEFT OUTER JOIN TourDetail AS td
ON (pt.TableId = td.TableId)
AND (GetDate() BETWEEN td.AssignedFrom AND td.AssignedTill)
LEFT OUTER JOIN Tournament AS t
ON (td.TournamentId = t.TournamentId)
WHERE td.TournamentId IS NOT NULL -- unassigned
-- or
WHERE td.TournamentId IS NULL -- assigned

This one works as the one before. If you do not need
any data from "Tournament" table, then you may skip
the second LEFT OUTER JOIN to boost performance.

The problem that appears in this schema is implementing
constraints that will prevent assigning same table to
different tournaments at the same time (or assigning
one table twice to some tournament). That could be
done by some trigger on "TourDetail" table.

In the first scenatio you'd have to check if the
poker table you are refering to in the inserted
record (updates should not appear or at least should not
affect assignment target) is not allready assigned
to some another tournament which duration intersects
with the duration of the torunament you are assigning
it to now.

In the second scenario you could make (TournamentId,
TableId, AssignedFrom) a primary key (or unique)
and always calculate "AssignedTill" (in a trigger)
based on allready entered "TourDetail" data for this
poker table (eg. when there's allready an assignment
made for this table in the future, you set the
"AssignedTill" of the current record to "AssignedFrom"
of the record allready in the table) and "Tournament"
data (to make sure that "AssignetTill" will not
break duration bounds of the tournament).

In the second scenario you could also try entering
a "dumb" entry into "Tournament" DB table which
will represent "non-tournament" assignments. This
could speed up queries which search for unassigned
poker tables, but it'll also make the triggers
much more complicated (each table would be initally
assigned to this "non-tournament" entry and the
trigger would have to update this assignment
and in most cases split it into two entries).

One more thing is the assignment time granularity.
If a table is always assigned for a whole day
(eg. it's assignment does not start nor end in
the middle of a day), then all "AssignedFrom",
"AssignedTill" (and "Duration...") would have
to have their time portion stripped (and
"AssignedTill" of an assignment record in
"TourDetail" would have to be equal to "AssignedFrom"
minus one day of the next assignment record for
this poker table) and remember to strip it
also from "GetDate()" (or "@check_date"). If the
assignment time granularity would be very fluent,
then you'd leave everything as it is (it could
probably lead to unexpected results if checked
date was exactly - to the miliseconds level - equal
to "AssignedFrom" or "AssignedTill").


Now, when we know much more details about your
model and problems, then maybe more people could
join the discussion and probably show errors
and/or weak points of my solution (or Daniel's
solution).



Kamil 'Hilarion' Nowicki

If NOT EXISTS
Identity column as a foreign key - help needed in logic
stored proc insert,update and cursor
1.9 million line transaction in profiler with no begin end using WAITFOR
decimal (11,2) to a fixed-formate