Designing a game database

Answer 1

select h.* from gamehistories as h join gamescores as g on g.gid=h.gid join users as u on g.uid=u.uid where u.uid=$uid

Right off the top of my head, dunno if there is any special sql magic that I am not using, but I typically use joins for this.

I dont see anything glaringly bad about your tables, but you do need a gamescore id field, just for the auto incrementing unique id if nothing else. Its my policy to give every table an id, but thats just me.

Answer 2

I would go with something like this

SELECT  u.displayname OpponentsName,
        gsYours.score MyScore,
        gs.score OpponenetsSCore
FROM    gamescores gs INNER JOIN
        (
            SELECT  gs.gid,
                    gs.uid,
                    gs.score
            FROM    gamescores gs 
            where   gs.uid = yourUserID
        ) gsYours   ON  gs.gid = gsYours.gid
                    AND gs.uid <> gsYours.uid INNER JOIN
        users u ON gs.uid = u.uid INNER JOIN
              gamehistories gh ON gs.gid = gh.gid AND gh.pubID = whatYouRequireHere

Not really sure how you wish to link to the gamehistories table.

Answer 3

Alright, armed with the fact that two players participate in each game, I suggest consolidating the gamehistories table with the gamescores table:

• users uid, displayname, email
• games gid, pubID, start, end, uid1, score1, uid2, score2

This results in fewer joins required to retrieve the information you want, but adds one nuance - you don't know if your user id will be in the uid1 or uid2 column, so you have to query both.

SELECT users.displayname AS `Opponent Name`,
    score1 AS `Your Score`,
    score2.score AS `Opponent Score`,
    TIMEDIFF(end, start) AS `Duration`
FROM
    users INNER JOIN games ON users.uid = games.uid1
WHERE games.gid = (TheGID)
    AND games.uid2 = (YourUID)
UNION
SELECT users.displayname AS `Opponent Name`,
    score2 AS `Your Score`,
    score1.score AS `Opponent Score`,
    TIMEDIFF(end, start) AS `Duration`
FROM
    users INNER JOIN games ON users.uid = games.uid2
WHERE games.gid = (TheGID)
    AND games.uid1 = (YourUID)