Re: Saving score of 3 players into a table

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.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.