Best way to organize my database / tables?

[Ender22]

Hello,

I'm working on a new project, and I can't quite wrap my head around how best to set up the database and tables...

What I have is a bunch of users, with all their info... and a bunch of games...
and I want to now store what high score each user has gotten in each game... so that every user can see their personal high score (and the scores of others when they visit the profile pages of their friends)

So I had a few ideas... One was to make a table for each game... with 1 column containing game id, one for user id, and one for users high score... but then I thought on the users profile page when I want to list the name and highscore of each game I will have no good way of traversing through all the games... unless I have another table which references all the individual game tables... sounds messy and wrong.

another idea was to just add the data into the already existing user tables... into like a single highscore column which would say something like gamename:score then i would search for the users id, then check the highscore column for the gamename, then grab the score after the :
but i'm not sure if that is good either... not exactly sure how to do that...

or maybe i should add a new column for each game into the users table... then just a single highscore value can be placed into the column... but then i'll have to adjust the number of rows for each user everytime a new game is made... not sure if that is good either...

It's like I needed the equivalent of a double array... which would have [userID][gameID] = score .... like [345][3] = 45 (meaning "user 345 has a score of 45 for game # 3")
but I'm a noob in mysql and I'm not sure how that translates...

any help from those more experienced is super welcome! thank you!

[Old Pedant]

CREATE TABLE users (
    userid INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(20),
    ... etc. ...
);

CREATE TABLE games (
    gameid INT AUTO_INCREMENT PRIMARY KEY,
    gamename VARCHAR(100),
    ... etc. ...
);

CREATE TABLE gamescores (
    userid INT REFERENCES users(userid),
    gameid INT REFERENCES games(gameid),
    whencompleted TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    score INT /* or REAL or ... */
);

Don't record just high scores; record *all* scores. (Unless this is a game where the user will play it 20 times a day.)

This makes it easy to see the highest scores in a given game *or* you can give a user a complete history of his/her scores.

Example:

/* highest scores in a given game */
SELECT S.score, U.username
FROM scores AS S, users AS U
WHERE S.userid = U.userid
AND S.gameid = 17 /*or any other gameid of course */
ORDER BY S.score DESC
LIMIT 10

If this a game where a user is likely to have hundreds of scores, then you could "prune" the scores back to, say, the top 50 for each player. Not hard to do each time you insert a new score for a user.

DELETE FROM scores 
WHERE userid = 777 
AND gameid = 17
AND whencompleted NOT IN (
    SELECT whencompleted
    FROM scores
    WHERE userid = 777 
    AND gameid = 17
    ORDER BY score DESC LIMIT 50 )

[Ender22]

oh wow... so using the REFERENCES keyword during the creation of the table will access the columns from another table... that's so cool I had no idea I could do that... perfect!

I will be storing only a single highscore though, as users may be playing the games 20 times a day, and I just want to save only the best one for some reasons.

can I then use this new table like normal?... example:

SELECT 'score' from 'gamescores' where 'userid'="$id" AND 'gameid'="$gameid"

because I don't quite understand the usage of the S. and U. here:

/* highest scores in a given game */
SELECT S.score, U.username
FROM scores AS S, users AS U
WHERE S.userid = U.userid
AND S.gameid = 17 /*or any other gameid of course */
ORDER BY S.score DESC
LIMIT 10

[Ender22]

Also,
every time I create a new users, will I have to add rows to the gamescore table to ensure that their profile page, which shows their game scores, can display 0's for each score without a mysql error?

[Old Pedant]

Okay, if you only have highest score per user per game that keeps it simpler.

This query:

/* highest scores in a given game */
SELECT S.score, U.username
FROM scores AS S, users AS U
WHERE S.userid = U.userid
AND S.gameid = 17 /*or any other gameid of course */
ORDER BY S.score DESC
LIMIT 10

Is used to show the highest 10 scores (change LIMIT 10 to LIMIT 25 to show highest 25, etc.) for a given game. A leaderboard for that game, in other words.

The "S" and "U" are called "aliases". You don't have to use them. You can write out the entire table name each time:

/* highest scores in a given game */
SELECT scores.score, users.username
FROM scores, users
WHERE scores.userid = users.userid
AND scores.gameid = 17 /*or any other gameid of course */
ORDER BY scores.score DESC
LIMIT 10

Note: If you do use an "AS" alias for a table name, then you *must* use that alias to refer to fields in the table. So all or nothing. I *always* use aliases, but that's me.

********

This query is WRONG!

SELECT 'score' from 'gamescores' where 'userid'="$id" AND 'gameid'="$gameid"

You can *NOT* use apostrophes around table or field names!

You *CAN* use backticks. The backtick ` usually is on the same keyboard key as the ~ tilde. BUT YOU DO NOT NEED TO USE backticks unless you foolishly give a name to a table or column that conflicts with a MySQL keyword or you use a name that uses invalid characters (e.g., spaces and minus signs, but many others).

You SHOULD NOT put quotes or apostrophes around NUMERIC values!

So that query, in PHP, would be correctly written as

$sql = "SELECT score FROM gamescores WHERE userid=$id AND gameid=$gameid";

(Because $id and $gameid *NEED* to be numbers in this query.)

**********

The REFERENCES clause does *NOT* "access the columns from another table".

*IF* your tables are INNODB tables (instead of MYISAM tables), then a column that REFERENCES a column in another table does two things:
(1) It ensures that you can't use a value in the REFERENCES column that does not *already exist* in the primary table. For example, in the tables I showed, you would not be able to add a record to the scores table for gameid 37 unless you already had a gameid 37 in the games table.

(2) Once a record exists that has a value that correctly references another table, you can't delete the primary record until *all* references to it have been removed. In other words, once the scores table has one or more records in it with userid 24, you won't be able to delete userid 24 from the users table. You would need to first delete all records with userid 24 from the scores table.

This is called "referential integrity". Look it up.

Again, this only applies to INNODB tables when using MySQL (it applies to all tables in most other DBs). *PROBABLY* all your tables will be INNODB tables. If in doubt, you can find out by using a data base query tool to issue a command such as

SHOW CREATE TABLE users;

And that will dump out the definition of the table including, near the end, what "engine" the table is using. For example (from my machine):

mysql> SHOW CREATE TABLE words;

CREATE TABLE `words` (
  `word` varchar(30) DEFAULT NULL,
  `wordcount` int(11) DEFAULT NULL,
  UNIQUE KEY `ix2` (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1