Hi !

I occasionnaly design some database, both for my work or personnal
projects. I've read here and there that it's not a good design to use
OIDs. So I'm currently trying to model a simple database without relying
on OIDs (usualy, I put OIDs everywhere and use them like one would use
addresses and pointers in a C program).

The database is to store chess games (I told you it was simple).

Basically, I have a table named 'players' with all possible players
(identified, say, by names), and a table named 'games' that stores merely
who played whites and who played blacks, and another one named 'moves',
which holds the moves for all games.

Obviously, each move must reference a game. And this is my problem : I
don't know how to uniquely define a game, without giving each game a
unique number like an OID or a sequence number or an arbitrary unique
name or any other such 'artificial' identifier.

More generally, my question is : what to do when you need a key
on a table but the table natural meaning don't have anything that
uniquely identify a row ?

Is it OK to use OIDs, then ?

Sory for my bad english, or if my question sounds too dumb.

rixed wrote:
would uniquely identify the game unless you have two masters playing
simultaneous lightning boards between each other.

On 2006-06-29, Bob Stearns <rstearns1241 (AT) charter (DOT) net> wrote:
I though of that (this is how a PGN 'database' identifies a game : A, B,
date, and event - and round if applicable).

But I already have a time stamp in the move table, so that the time stamp
of the game, being the time stamp of the first move, would be a mere
repetition.

Or, as there are always a first move in a game (a game that is
abandoned before the first move can not reasonably be called a game),
I store the first move in the game table, so that there are no
redundancy between the date of the game and the date of the first move.

What do you think ?

First let me congratulate you on realizing that arbitrary ID's
(pseudokeys) in a database are essentially pointer values similar to
pointers in programing languages like C!

Pseudokeys have their uses, but they should be used carefully.

rixed wrote:
yes that is the more natural key.

Why timestamp the moves? Unless you are going to try to calculate the
time remaining on the players clock, it seem excessive. I do not know
any tournaments that have players record the time of their moves. Now
obviously you are setting up some online system, so you could record
this easily. But I still have to question why?

If you record time for each move, perhaps you should record merely
elapsed time.

the time of the game and the time of the move are not necessarily
redundant data. It depends on why you record that data. Consider: when
would you run a report that shows the time of each move? (where the
time is the relevant information) As a Chess player, i've never been
interested in when I made my moves. But I do still have notations from
a few games where it records where the game was played and when the
game was played (date, but no time).

Consider how you will use the data. Consider writing a couple typical
queries. Ask your users, do they want that information? Collecting
information, just because you can, is seldom a good reason.

That's what I think.
Ed

rixed wrote:

game key(s), white's move, white's time ending, black's move, black's
time ending

The game's start time stamp is the time which is subtracted from white's
first move's time ending to give the first move's elapsed time.

On 2006-06-30, Bob Stearns <rstearns1241 (AT) charter (DOT) net> wrote:
I should have given an important information : this database is
designed to store email-chess games. That's why I store the time stamp
of each move, because on such games the clock constraints are how many
moves must occur in a week.

This also invalidates your otherwise perfect suggestion, because this
kind of game really starts with the first white moves.

On 2006-06-30, Ed Prochak <edprochak (AT) gmail (DOT) com> wrote:
I should have said it right from the start : this is for email-chess,
and I must know how many moves were executed in the last seven days as
this is the (only) time constraint.

Storing time stamp is easier, I think.
Easy at insertion, and easy to know how many moves occurred during the
last 7 days. Both actions are more complicated with elapsed time.

I though of something else : adding a sequence number that counts how
many times A and B played together. This does not look artificial, the
key designation of a game would be, for example, 'the third party A
played white against B black', which makes sense I think.

rixed wrote:
Yes, an important requirement. Postal chess is different.

now that we know the requirement, I agree.

DO you mean 'the third game of the match played between A (white) and B
(black)' ?

An arbitrary sequence number doesn't make sense. But a number for the
games within a match makes sense.

Looks like you ar making progress.
Ed