Re: Database design for a little soccer league

On Mar 21, 3:53 am, "Mariano C." <mariano.calan..._at_gmail.com> wrote:
> I'm developing an application about little soccer league, i need to
> track teams, team's players, match, game's events (goal, red card,
> yellow card, ...), player who took part in a match.
>
> I have 5 tables:
> team (stores teams information. PK: id)
> player (stores players information. PK: id; FK: team_id)
> matches (stores information about match, this is a joining table among
> team and team. PK: id; FK: home_team_id, away_team_id)
> events (stores game's events for given match and who player was
> responsable for this. PK: id; FK: match_id, player_id)
> lineup (stores players who took part in a given match. PK: id; FK:
> player_id, match_id)
>
> Does it seems correct?
> This is the DB with the PK and FK (hope that link is working):http://imgclub.org/images/caressarev.jpg

If a team member moves from one team to another (ie. his/her parents sign the child up next year, but decide to go to a different team - also based near to where they live) will you just update the players table?

If you did, the events table would record past goals as having been scored by the new team. If the match table records the full-time score (a possible optimisation) then there will be an inconsistency. Perhaps you will have to create a new player record and lose the player history. Ugh! Alternatively players cannot change to another team once signed up... or you need to extend the model a little bit so it includes the players history. Received on Mon Mar 21 2011 - 23:07:37 CET