CODEWITHSUNDEEP
phpmysql
zhujik
zhujik

Reputation: 6574

Managing a rankings list with MySQL

For a sports club I am in, I want to manage a rankings list with MySQL on the website.

We have a tournament system where players can participate (or not) once a week and get points. Those points are somehow processed and a rankings list should be created on the website.

I am not so experienced with MySQL so I want to know if the system I thought of is good or another approach would be better.

I thought of having two tables:

players
-------
ID (unique primary key)
Name
Surname
+other arbitrary stuff

tournaments
-----------
ID (unique primary key)
Date
player (secondary key,  points to player ID)
+ columns for results of that player in this particular tournament

on the website I would then query the tournaments table, sort for ID and Player and process the results to compute a rankings list.

Is this a good approach or is there a better, more convenient way to do this?

Upvotes: 1

Views: 181

Answers (1)

iMakeWebsites
iMakeWebsites

Reputation: 587

You may find later that it would be more convenient to have one more table.

I would change your tournament table to something more like:

tournaments
-----------
ID (unique primary key)
Date
~Location
~+Other stuff about the tournament as a whole

And then add a table:

results
-----------
player_id (points to player ID)
tournament_id (points to tournament ID)
+results

The reasoning is because it will make it easier to filter on a single tournament, and many players could share the same tournament info without duplicating the results on each player.

Upvotes: 6

Related Questions