Hi all!

 

I'm creating a personal project website in Drupal which may, or may not ever go live. It contains various quiz-like games to help memory where users can register and save their scores etc. I want to keep a record ideally of all the scores they submit so they can see improvement, displayed in graphs, and compare with other users etc.

 

As I don't have have much experience in db design, I was wondering what would be the most efficient way of building the score table/tables. My main concern is that the table could become huge, with possible many users, many different quizzes, and multiple entries, and may be slow to query.

 

Currently I have one table with columns (or their Drupal equivalents) User ID, Game ID, Score, Time taken, and timestamp, with every submission being dumped into it. This is working well form now, but wonder what it will be like if it becomes massive. Is there a better way to be doing this, or does this sound alright?

 

Thanks!

Sounds about right to me, but then again I'm no expert.

Unless you have some idea to re-use the old data

I would say delete any entry over six months old.

I would split the database up if you think you need more speed. It depends how many entries you think you may have but you can speed things up considerably by splitting up the tables, maybe a different one for each month or something but if you are just dealing in numbers then it will still be fast even with 100,000 or so rows.

I would split the database up if you think you need more speed. It depends how many entries you think you may have but you can speed things up considerably by splitting up the tables, maybe a different one for each month or something but if you are just dealing in numbers then it will still be fast even with 100,000 or so rows.

 

Ah right I think thats what I needed to know. If it starts getting big then I'll think about it it then

maybe by each month isnt the right word, but if you have millions of records you can speed things up considerably by splitting the tables up and calling the relevant table, searching a much smaller dataset. eg if he only wants to call the data from this month then there is no point in searching through months and months of records.

maybe by each month isnt the right word, but if you have millions of records you can speed things up considerably by splitting the tables up and calling the relevant table, searching a much smaller dataset. eg if he only wants to call the data from this month then there is no point in searching through months and months of records.

Um im pretty sure this don't speed up anything