Game Database Design

Question

I am trying to create a database for recording game histories for a game that I am working on. I have 3 different backgrounds: users, game stories and games.

Columns for tables:

users : uid, displayname, email
gamehistories : gid, pubID, start (datetime), end (datetime)
gamescores : gid, uid, score

I am trying to create the following result set given by userID (uid):
Opponent name, my account, opponent’s account, duration

Any ideas? Is my design good? How can I query these tables to get game histories for a given uid?

Answer 1

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 sure how you want to link the game table.

Answer 2

 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 

From the very beginning, I don’t know if there is any special sql magic that I don’t use, but I usually use for this connection.

I don't see anything wrong with your tables, but you need an idcorecore field, just to automatically increment a unique identifier, if nothing else. Its my policy to give each table an identifier, but that's just me.

Answer 3

Well, armed with two players participating in each game, I propose to consolidate the gamehistories table with the gamescores table:

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

This leads to fewer connections required to obtain the required information, but adds one nuance - you do not know if your user ID will be in the uid1 or uid2 , so you need to request 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)