Re: Advice requested on system.

frizzle wrote:
>
> I have this soccer site, and in 1 part one
> can manage the played matches:
> Score, location, players who scored etc.
>
> Now the last part is what i have a question about.
> The players are in a MYSQL db. Each has it's own
> unique ID.
> Fields:
> - id
> - firstname
> - lastname
> - position

Bad idea. If a player changes a position, it will affect
your records of all previous matches in which that player
participated. Not to mention players changing teams...

> Matches are also stored in the DB.
> Fields:
> - id
> - opponent
> - location
> - date
> - homepoints
> - visitorspoints
>
> If i have a new match, and the score is 5-3 how should
> i add 5 player's ID of players who scored (in a yet to
> create DB-field), in that way that i can get the person
> that scored the most ... (topscorer)
> And 2nd, and 3rd etc.

Here's what I would do...

Table players:
  id
  firstname
  lastname

Table teams:
  id
  name

Table matches:
  id
  hometeam (joins with teams.id)
  visitor (joins with teams.id)
  location
  date

Table rosters:
  id
  match (joins with matches.id)
  player (joins with players.id)
  team (joins with teams.id)
  position

Table goals:
  id
  match (joins with matches.id)
  scored_by_team (joins with teams.id)
  scored_by_player (joins with players.id)

Note that score is not recorded anywhere; it should be computed
by quering the `goals` table. Note also that this data design
allows you to record (and find) goals that players accidentally
scored against their own teams.

Now, figuring out top scorers becomes rather easy:

SELECT
  players.firstname AS first,
  players.lastname AS last,
  COUNT(goals.id) AS goals_scored
  FROM goals LEFT JOIN players
    ON goals.scored_by_player = players.id
  GROUP BY players.id
  ORDER BY goals_scored DESC;

Cheers,
NC
Received on Tue Oct 18 02:17:13 2005