Hello All,
I'm having some problems laying out a particular table. I'm using PHP and MySQL. I have to record test score for each user with a past history of scores. The number of tests is constantly changing. I'll need to display the data two ways. First, all of the results for a particular test, and second, the top score for each test.

So I was planning on creating a simple table for each user with three columns - the test id#, a datetime stamp, and the score. The only problem that comes to mind is that there is no unique column for a primary key.

But I could use something like this to show a historic list of scores for a particular test id#:

SELECT * FROM table WHERE testid='var' ORDER BY DESC

or this to show the top score for each test id#:

SELECT * FROM table WHERE testid='var' ORDER BY DESC LIMIT 1

So is there a better way to do this?

    So I was planning on creating a simple table for each user with three columns

    that's not a good idea, lets read abot Database normalisation

    If you have a users table,

    users
    users_id (primary key)
    name
    email

    store the scores in a separate table, where users_id is in a relation with users table, and test_table_id is connected with the test_table table's primary key.

    test_scores
    test_scores_id (primary_key)
    usersid (foreign key)
    test_table_id (foreign key)
    score
    date_time

    Then a query should looks like:
    Selet the user's all score with DESC order:
    And the users primary key will entered in the WHERE clause: users.users_id = 1

    Select users.users_id,
  users.name,
  test_scores.score,
  test_scores.date_time,
  test_scores.test_table_id
From users Inner Join
  test_scores On test_scores.userid = users.users_id
Where users.users_id = 1
Order By test_scores.score Desc

    This is a better way how you store scores/test_ID/userID, but after reading about the normaliisation you will know why is needed this third table to store them.
    And a design view is in the attached image.

    Hello,
    jjozsi.

      Thanks for the reply,
      I didn't understand what the normalization article had to do with what I asked but I guess that doesn't really matter that much. I have a few questions. You made reference to a third table, and I didn't understand what you meant. And with a few thousand users at a time on the site would this put a heavier load on the database then individual tables? I'm not an expert my any means but I know that this table will soon have hundreds of thousands of rows in a very short amount of time and it just seemed to me like it would be much harder on the server to go through this massive table vs. a much smaller table. I'm not arguing just curious.

        timska77 wrote:

        I didn't understand what the normalization article had to do with what I asked but I guess that doesn't really matter that much. I have a few questions. You made reference to a third table, and I didn't understand what you meant.

        That is what the normalisation was about: you have a many to many relationship between users and tests, so you should have one table for users, one for tests, and one to associate users and tests.

        timska77 wrote:

        And with a few thousand users at a time on the site would this put a heavier load on the database then individual tables? I'm not an expert my any means but I know that this table will soon have hundreds of thousands of rows in a very short amount of time and it just seemed to me like it would be much harder on the server to go through this massive table vs. a much smaller table.

        I am not an expert on database optimisation either, but database engines are supposed to be optimised for a normalised database design, assuming that you set the appropriate indices.

          OK. Hopefully my last question. If I have a table to associate users to tests how should I do this meaning how do I associate them? Is this just a table with two columns like user_id and test_id.

            timska77 wrote:

            If I have a table to associate users to tests how should I do this meaning how do I associate them? Is this just a table with two columns like user_id and test_id

            Yes, though you may have more columns, e.g., for the score. I presume that each test is taken on the same date for all users, if not you would put that as a column in this "join table" that associates users and tests.

              OK. Please be patient, I can be very dumb from time to time and this appears to be on of those times. I will always have a "user" table so now if I have a table with the test_id, datetime stamp, user_id, and score, why do I need a third table? I realize if this is frustrating dealing with a dummy so I'll understand if you don't want to answer.

                timska77 wrote:

                I will always have a "user" table so now if I have a table with the test_id, datetime stamp, user_id, and score, why do I need a third table?

                Are you saying that each user can only take exactly one test (and only take that test once)? If so, then yes, you do not need a third table.

                  No the users can take an unlimited amount of tests an unlimited number of times.

                    No the users can take an unlimited amount of tests an unlimited number of times.

                    Ah, then to see why a third table is useful consider: suppose a user takes two tests. How are you going to record that in a single test id column in the users table? Okay, maybe you use multi-valued fields by having a comma delimited list of test ids in the test id column. Now, what about the scores? If you do the same thing... you will soon find that it is difficult to retrieve specific test data concerning a user, since you would need to do string manipulation. The fact that a user can take a test twice complicates the situation even more.

                    Now, suppose you took our suggestion and used a third table. A test taken by a user would be very obviously identified with the user's score for that test because the test id, user id, date of attempt and score would be in the same row. There would not be multi-valued fields, so you just need to join to retrieve the user specific test data. Things like counting, sorting and searching become easier and faster.

                      I'm doing nothing special, just drop these tables into MS ACCESS visual designer mode.
                      To build this thing takes about 8 minutes...
                      I suggest have a look into ACCESS table and SQL designing.

                      Notice that, this is not a clean version, becouse you lost the sub-scores from the test results.

                      If you have a users table:

                      userid	username
1	a
2	werr
3	rrr

                      You have a test_table table

                      test_id	test_name
1	qwe
2	e
3	sdfsdf
4	ffsdf

                      And you have a scores table:

                      user_id	test_id	summed_score
1	1	4
1	1	66
1	1	323
1	2	3
2	1	3
2	1	444
2	2	3

                      A query, which selects all the scores joining the test_table and the users all details:
                      (the user can fill the tests unlimited times, and the scores will be aggregated)

                      SELECT users.username, users.userid, test_table.test_id, test_table.test_name, Sum(scores.summed_score) AS SumOfsummed_score
FROM users INNER JOIN (test_table INNER JOIN scores ON test_table.test_id = scores.test_id) ON users.userid = scores.user_id
GROUP BY users.username, users.userid, test_table.test_id, test_table.test_name;

                      The data is:

                      username	userid	test_id	test_name	SumOfsummed_score
a	1	1	qwe	393
a	1	2	e	3
werr	2	1	qwe	447
werr	2	2	e	3

                      How to select the high scores from these three tables:

                      SELECT users.userid, users.username, test_table.test_id, test_table.test_name, Max(scores.summed_score) AS MaxOfsummed_score
FROM users INNER JOIN (test_table INNER JOIN scores ON test_table.test_id = scores.test_id) ON users.userid = scores.user_id
GROUP BY users.userid, users.username, test_table.test_id, test_table.test_name;
                      userid	username	test_id	test_name	MaxOfsummed_score
1	a	1	qwe	66
1	a	2	e	3
2	werr	1	qwe	444
2	werr	2	e	3

                        Thanks. I'll play with these with PHPmyADMIN in a few minutes.

                          timska77;10903611 wrote:

                          Thanks. I'll play with these with PHPmyADMIN in a few minutes.

                          notice that the field names might different with your version.

                          good luck 🙂

                            Well, I'm familiar with database calls like that as I've modified a few in the past that were similar so I'm sure I can probably figure that out but one thing I'm unsure of is using a call like that how do I get results for just one, or a few, specific users.

                              OK, forget my last. I didn't think it was ok to use calls like that just like normal calls but apparently it is so I think I'm set. Thanks for the replies everyone and for helping me to make a more efficient system in the process.

                                Don't forget to set this thread as resolved.

                                Hello, jjozsi.

                                  Write a Reply...