This is my current database structure
[php]
create table tournament (
tid int(5) not null auto_increment,
period varchar(20) not null,
Primary key (tid));

create table game (
gid int(6) not null auto_increment,
tid int(5) not null references tournament(tid),
Primary key(gid));

create table results (
gid int(6) not null references game(gid),
pid int(5) not null references player(pid),
point int(2) not null,
Primary key(gid,pid));

create table player (
pid int(5) not null auto_increment,
nick varchar(30) not null,
Primary key(pid)); [/php]


My objective is to create a ranking table that is sorted by user's total point which is based on the top 5 points of the user in the most recent tournament period. Value to retrieve is as below

Ranking | Nick | Total Game Played (in that tournament) | Total points(based on the best 5 points of that user) | List the top 5 points

This will list all the users that have participated in this tournament period. I know is a tricky one, only reason why im here. You can use any php loops or nested statement to do this.

I am already having an alternative solution which is creating a ranking table which fills in the value but im looking for a better way to do it.


[php]create table ranking (
rid int(6) not null auto_increment,
tid int(5) not null references tournament(tid),
pid int(5) not null references player(pid),
totalplay int(2) not null,
point1 int(2) not null,
point2 int(2) not null,
point3 int(2) not null,
point4 int(2) not null,
point5 int(2) not null,
Primary key (rid));[/php]