|
|
 | | From: | Terrence Brannon | | Subject: | Database table for nested statistical query | | Date: | Fri, 21 Jan 2005 19:41:38 +0000 |
|
|
 |
I am designing a Postgresql schema for a website which will log
results of match interactions between players. Because players belong
to clans, I will do clan stats as well, but that's later.
When a player logs in, he will be
presented with these statistics: the last 10 battle results, his
personal 10 last battle results, the 3 people he has beaten the most,
the 3 people who have beaten him the most.
The last 2 statistics make it difficult for me to figure out how to
design the battle_t table. A battle has 2 players and a result of win,
lose, or draw represented by 1, 0, or -1.
Here is my current schema design. I would appreciate input into how
the battle_t table should be designed.
And could someone recommend a good text on database design?
CREATE TABLE player_t (
player_id SERIAL NOT NULL,
clan_id INT4,
screen_name VARCHAR(40) NOT NULL,
login_name VARCHAR(40) NOT NULL,
password VARCHAR(40) NOT NULL,
email VARCHAR(40),
elo INT4 DEFAULT 1600 NOT NULL,
date_joined TIMESTAMP,
PRIMARY KEY (player_id)
);
CREATE TABLE clan_t (
clan_id SERIAL NOT NULL,
clan_name VARCHAR(40),
clan_url VARCHAR(40),
player_id INT4,
PRIMARY KEY (clan_id)
);
CREATE TABLE battle_t (
battle_id SERIAL NOT NULL,
player1 INT4 NOT NULL,
player2 INT4 NOT NULL,
result INT4 NOT NULL,
timestamp TIMESTAMP NOT NULL,
PRIMARY KEY (battle_id)
);
/*==========================================================================*/
/* Foreign Keys
*/
/*==========================================================================*/
ALTER TABLE clan_t
ADD FOREIGN KEY (player_id) REFERENCES player_t (player_id);
/*==========================================================================*/
/* Indexes
*/
/*==========================================================================*/
CREATE UNIQUE INDEX IDX_player_t1 ON player_t (player_id);
CREATE INDEX IDX_clan_t1 ON clan_t (player_id);
--
Carter's Compass: I know I'm on the right track when,
by deleting something, I'm adding functionality.
|
|
| | From: | --CELKO-- | | Subject: | Re: Database table for nested statistical query | | Date: | 21 Jan 2005 17:55:32 -0800 |
|
|
| I think is more like what you want -- no reserved words, no proprietary
data types, DRI actions, reasonable lengths (a CHAR(40) password?!),
etc.
CREATE TABLE Players
(player_id INTEGER NOT NULL PRIMARY KEY,
clan_id INTEGER NOT NULL
REFERENCES Clans(clan_id)
ON UPDATE CASCADE,
screen_name VARCHAR(25) NOT NULL,
login_name VARCHAR (20) NOT NULL,
password VARCHAR(8) NOT NULL,
email VARCHAR(100) NOT NULL,
elo INTEGER DEFAULT 1600 NOT NULL,
membership_date TIMESTAMP NOT NULL);
CREATE TABLE Clans
(clan_id INTEGER NOT NULL PRIMARY KEY,
clan_name VARCHAR(20) NOT NULL,
clan_url VARCHAR(40) NOT NULL);
CREATE TABLE Battles
(battle_id INTEGER NOT NULL PRIMARY KEY,
winner_player_id INTEGER NOT NULL,
REFERENCES Players(player_id)
ON UPDATE CASCADE,
loser_player_id INTEGER NOT NULL
REFERENCES Players(player_id)
ON UPDATE CASCADE,
CHECK (winner_player_id <> loser_player_id),
battle_result INTEGER DEFAULT 0 NOT NULL
CHECK(battle_result IN (0, 1)), -- zero is a tie
battle_date TIMESTAMP NOT NULL);
>> ... these statistics: the last 10 battle results, his personal 10
last battle results, the 3 people he has beaten the most, the 3 people
who have beaten him the most. <<
Here are the skeletons for getting your stats. You can then add a join
to Players to get the names. The front end should handle picking the
number of rows to be displayed, not the backend.
SELECT winner_player_id, loser_player_id, battle_date
FROM Battles
WHERE :my_guy IN (winner_player_id, loser_player_id)
Would it be better to display the screen names? How do we handle ties?
SELECT P.screen_name, COUNT(*) AS losses_to_me
FROM Battles AS B, Players AS P
WHERE B.winner_player_id = :my_guy
AND B.loser_player_id = P.player_id
AND B.battle_status <> 0
GROUP BY P.screen_name;
SELECT P.screen_name, COUNT(*) AS wins_over_me
FROM Battles AS B, Players AS P
WHERE B.loser_player_id = :my_guy
AND B.winner_player_id = P.player_id
AND B.battle_status <> 0
GROUP BY P.screen_name;
|
|
| | From: | --CELKO-- | | Subject: | Re: Database table for nested statistical query | | Date: | 23 Jan 2005 16:14:08 -0800 |
|
|
| >> A player has many battles and a battle has many (2) players. The way
to handle a many-to-many relationship is to create an intermediate
table that each table has a one-to-many relationship with. Don't you
think that is a better way to handle this? <<
No, two players per battle is not "many" -- many means we have "1 to m"
where we do not have a constant value of (m).
|
|
| | From: | Terrence Brannon | | Subject: | Re: Database table for nested statistical query | | Date: | Sat, 22 Jan 2005 00:41:33 +0000 |
|
|
| Terrence Brannon writes:
> I am designing a Postgresql schema
I clearly need some education in this area. The schema below is not
normalized.
> for a website which will log results of match interactions between
> players. Because players belong to clans, I will do clan stats as
> well, but that's later.
>
> When a player logs in, he will be
> presented with these statistics: the last 10 battle results, his
> personal 10 last battle results, the 3 people he has beaten the most,
> the 3 people who have beaten him the most.
>
> The last 2 statistics make it difficult for me to figure out how to
> design the battle_t table. A battle has 2 players and a result of win,
> lose, or draw represented by 1, 0, or -1.
>
> Here is my current schema design. I would appreciate input into how
> the battle_t table should be designed.
Here follows the newer more sane schema. I think I can cobble up s SQL
query for it.
/*==========================================================================*/
/* Tables
*/
/*==========================================================================*/
CREATE TABLE player_t (
player_id SERIAL NOT NULL,
clan_id INT4,
screen_name VARCHAR(40) NOT NULL,
login_name VARCHAR(40) NOT NULL,
password VARCHAR(40) NOT NULL,
email VARCHAR(40),
elo INT4 DEFAULT 1600 NOT NULL,
date_joined TIMESTAMP,
PRIMARY KEY (player_id)
);
CREATE TABLE clan_t (
clan_id SERIAL NOT NULL,
clan_name VARCHAR(40),
clan_url VARCHAR(40),
player_id INT4,
PRIMARY KEY (clan_id)
);
CREATE TABLE battle_t (
battle_id SERIAL NOT NULL,
player1 INT4 NOT NULL,
player2 INT4 NOT NULL,
result INT4 NOT NULL,
timestamp TIMESTAMP NOT NULL,
PRIMARY KEY (battle_id)
);
CREATE TABLE player_battle_t (
player_id INT4 NOT NULL,
battle_id INT4 NOT NULL,
battle_time TIMESTAMP,
winner INT4,
PRIMARY KEY (player_id, battle_id)
);
/*==========================================================================*/
/* Foreign Keys
*/
/*==========================================================================*/
ALTER TABLE clan_t
ADD FOREIGN KEY (player_id) REFERENCES player_t (player_id);
ALTER TABLE player_battle_t
ADD FOREIGN KEY (player_id) REFERENCES player_t (player_id);
ALTER TABLE player_battle_t
ADD FOREIGN KEY (battle_id) REFERENCES battle_t (battle_id);
/*==========================================================================*/
/* Indexes
*/
/*==========================================================================*/
CREATE UNIQUE INDEX IDX_player_t1 ON player_t (player_id);
CREATE INDEX IDX_clan_t1 ON clan_t (player_id);
CREATE UNIQUE INDEX IDX_battle_t1 ON battle_t (battle_id);
CREATE INDEX IDX_player_battle_t1 ON player_battle_t (player_id);
--
Carter's Compass: I know I'm on the right track when,
by deleting something, I'm adding functionality.
|
|
| | From: | Terrence Brannon | | Subject: | Re: Database table for nested statistical query | | Date: | Sun, 23 Jan 2005 23:59:58 +0000 |
|
|
| "--CELKO--" writes:
> I think is more like what you want -- no reserved words, no proprietary
> data types, DRI actions, reasonable lengths (a CHAR(40) password?!),
> etc.
>
> CREATE TABLE Players
> (player_id INTEGER NOT NULL PRIMARY KEY,
> clan_id INTEGER NOT NULL
> REFERENCES Clans(clan_id)
> ON UPDATE CASCADE,
> screen_name VARCHAR(25) NOT NULL,
> login_name VARCHAR (20) NOT NULL,
> password VARCHAR(8) NOT NULL,
> email VARCHAR(100) NOT NULL,
> elo INTEGER DEFAULT 1600 NOT NULL,
> membership_date TIMESTAMP NOT NULL);
>
> CREATE TABLE Battles
> (battle_id INTEGER NOT NULL PRIMARY KEY,
> winner_player_id INTEGER NOT NULL,
> REFERENCES Players(player_id)
> ON UPDATE CASCADE,
> loser_player_id INTEGER NOT NULL
> REFERENCES Players(player_id)
> ON UPDATE CASCADE,
> CHECK (winner_player_id <> loser_player_id),
> battle_result INTEGER DEFAULT 0 NOT NULL
> CHECK(battle_result IN (0, 1)), -- zero is a tie
> battle_date TIMESTAMP NOT NULL);
>
A player has many battles and a battle has many (2) players. The way
to handle a many-to-many relationship is to create an intermediate
table that each table has a one-to-many relationship with. Don't you
think that is a better way to handle this?
|
|
|
