3

I'm creating a tennis ladder website using PHP/MySQL/CSS & HTML. A tennis ladder is basically a system where you challenge a player and if you beat them you go one spot above them on the ladder rankings list. Originally I designed the website so that it was a single ladder that users could register to join and then participate in. That was up and running fairly well (in tests), but now I've decided I want to further expand it to allow multiple ladders. Here's what I'm trying to accomplish:

  • A user can join many different ladders.
  • A ladder can have many different users.
  • Each ladder has a type, name, and description.
  • A ladder match will keep record of who won, who lost, and the score and date played of the match.
  • Stats will be collected for each player within that SPECIFIC ladder.

Here's what the tables for the ladder DB look like:

Table diagram

Here's a rudimentary visual representation of what some test data would look like when displayed. This will change to represent a select ladder's data.

UI Mockup

This is the first real web app I've attempted, so any advice on schema structure would be appreciated. Originally when I had conceived of it as just 1 ladder that everyone could join I had combined the User table fields with the UserLadderStats fields, and there were no Ladder or Ladder_Users tables. LadderMatch table has remained almost the same, but it now has foreign key references.

Improve this question
3
  • You can probably use just one table for the purpose of Ladder_users and UserLaderStats. They both contain the (UserID, LadderID) pair; having two tables might be redundant.
    – joanolo
    Commented Jan 12, 2017 at 21:58
  • 2
    Good luck with your db work, but please check some better than your pre-Elo-ladder solution for your ranking system. :) - Here's a comparison between Elo, Glicko, TrueSkill and ree rating system.
    – Tomaso Neri
    Commented Jan 13, 2017 at 22:06
  • Thanks, I plan on introducing different ranking systems a bit down the line. This is my first attempt at creating a web app -- I've only had one programming class in my life, so all of this PHP/SQL/HTML/CSS/Javascript stuff is still fairly new to me.
    – Eric
    Commented Jan 18, 2017 at 18:34

1 Answer 1

Reset to default
1

This looks good, except I would combine the Ladder_Users table with the UserLadderStats table. There's no reason to have them separate and it just introduces the chance for data integrity problems. Just move all the stats onto the Ladder_Users, and then you can drop UserLadderStats.

Also, note that the WinnerUserID and LoserUserID in LadderMatch are foreign keys, even though they aren't explicitly marked as such.

Improve this answer
1
  • Thank you -- not sure why I didn't think to do that before.
    – Eric
    Commented Jan 18, 2017 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.