Re: Saving score of 3 players into a table

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alexander Farber
Sent: Tuesday, October 25, 2011 3:33 PM
To: pgsql-general
Subject: [GENERAL] Saving score of 3 players into a table

Hello,

I'm trying to save results of card game with 3 players into a table.

It is bad enough, that I had to introduce
3 columns for user ids: id0, id1, id2 and
3 columns for their scores: money0, money1, money2 -

create table pref_results (
id0 varchar(32) references pref_users,
id1 varchar(32) references pref_users,
id2 varchar(32) references pref_users,
money0 integer not null,
money1 integer not null,
money2 integer not null,
rounds integer not null,
finished timestamp default current_timestamp
);

But now I've also realized, that I don't know, how to join that table with
the pref_users, so that I get first_name for each of 3 players -

[...]

I'm probably doing something wrong here?

Thank you
Alex

--------------------/Original Message ----------

Yes, you are creating multiple columns to hold data for each of the players.
Each player should go into a separate row.

You want something like:

CREATE TABLE pref_results (
Game_id varchar,
Player_id varchar,
Player_winnings numeric,
Player_position integer -- not truly required but useful for
generating columns later
);

CREATE TABLE pref_games (
Game_id varchar,
Game_rounds integer,
Game_finished_ts timestamptz
);

It is almost always wrong to have columns where you are simply adding a
sequential integer to the same base name.

However, to answer your question, you would need to JOIN the "pref_users"
table to the "pref_results" table THREE TIMES, once for each of (id0, id1,
id2).

SELECT *
FROM pref_results
JOIN pref_users user_0 ON (id0 = user_0.id)
JOIN perf_users user_1 ON (id1 = user_1.id)
JOIN perf_users user_2 ON (id1 = user_2.id)

Note the aliases for the pref_users table, and you would want to alias any
columns you end up pulling into the SELECT list.

Then you hope you never need to add a 4th player.

If you still want to present the data using 3 sets of columns for the
players you would need to perform a limited self-join:

SELECT
Game_id,
p1.Player_id AS P1_ID,
p2.Player_id AS P2_ID,
p3.Player_id AS P3_ID
FROM (SELECT ... FROM pref_results WHERE Player_position = 1) p1 USING
(Game_id)
JOIN (SELECT .. FROM pref_results WHERE Player_position = 2) p2 USING
(Game_id)
JOIN (SELECT .. FROM pref_results WHERE Player_position = 3) p2 USING
(Game_id)

Then add whatever columns and JOIN you need to get all the desired fields
into the output.

In this way you have a database model that is easy to query and insert data
into while still having the ability to view the data in a natural way
(horizontally). Add should you want to track a game with four players you
can still use the same data model and simply add a VIEW similar to the
three-person view but with a fourth set of columns for the fourth player.

David J.