rocknbil - 4:51 pm on Jan 6, 2011 (gmt 0)
I would think you'd start with the schedules first.
id int(11) | game_name (varchar) | game_date (datetime)
Then if there are multiple players for a match, you'd have a table that holds that information, which could be updated with a score for each player. No need to store "winner," that would be determined by the score. Store the players by their ID, not full name, it will make lookups faster.
id (int(11) | player_a (int) | player_b (int) | player_a_score (int) | player_b (int) | challenged_by (int) | status (tinyint; 1=accepted, 2=declined, etc.) | notes (text)
Now you'd have the players.
id (int(11) | fname (varchar) | lname(varchar) | city (varchar) | province_state (varchar) | phone | address | profile . . . whatever info you want to store here
Using these three, you could
- show available games and players who are not matched against the current player for a challenge
- Set up a system of challenge/accept/decline
- query for pending games or historic results
Probably pieces missing, but it's a start.