Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
maximemichel
Starting Member
3 Posts |
 Posted - 2009-09-20 : 22:19:54
|
| Hello. I am kinda new to the SQL structure and I had a big question.I will be running a site with a Flash game, that will send data to a PHP script that will then send it to the database. Let's say my game has 500 levels. There will also be new levels every few days. I plan the userbase to be around 20,000 people, for a start. I want to keep the highscore of everyone on each of these levels. So Person1 would have up to 500 scores in the db. I would then access those scores from different PHP scripts. There would be a page for the best score on each level, a page showing Person1 best scores for all levels, and probably other things in the future.My question is: should I make one huge table with judicious indexes and dump everything into it or make one table for each level? I thought at first making multiple tables, but having multiple tables means I will have to query over 500 tables if I want the best score of Person1.I don't know exactly how speed works in SQL, so any help would be welcomed.PS: And while at it, is there any command to see which rank is a certain entry? Like if I select a table ordered by score is it possible to easily know Person1 is which rank? |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-09-21 : 03:02:08
|
| You should probably stay with one table only, as I assume you will be collecting the same information from each level in the game. Keeping it all in separate tables would just be a complete mess. With 20k people having 500 rows in each table it will only give you a table with around 1 mill rows, and that's hardly anything these days. And if you're on 2005 or above the ranking should be pretty easy:SELECT Col1, Col2, Rank AS (RANK() OVER PARTITION BY UserID ORDER BY Score)FROM tableWHERE UserId = xx- Lumbago |
 |
|
|
maximemichel
Starting Member
3 Posts |
Posted - 2009-09-21 : 03:25:38
|
| Could you please explain how the rank query works? I have searched for an explanation, but haven't found anything useful.Let's say I have a column named User and a column called Score in a table called DTable. If I wanted to have the rank of a user called DummyTest, would it looks like:SELECT Rank AS (RANK() OVER PARTITION BY User ORDER BY Score)FROM DTableWHERE User=DummyTestAnd I still want to know what I am using to do that. PS: I am running MySQL 4.1.22. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-09-21 : 03:51:13
|
| Well...if you're on MySQL then it will for sure not work. This forum is for MS SQL Server only. You should try forum.mysql.com instead, but I can tell you that getting the ranking to perform properly in MySQL 4.1.22 could be a real challenge.- Lumbago |
|
|
|
maximemichel
Starting Member
3 Posts |
Posted - 2009-09-21 : 15:09:58
|
| Oh I am sorry. Google failed on me. Well, thanks a lot for your help. |
|
|
|
|
|
|
|
|
Multiples tables or a big one?