Re: Database design for a little soccer league

Mariano C. 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

All those "id" columns look wrong. Introduce synthetic keys only when no suitable natural key exists. Not knowing the details of the real-world situation with your league I can't say if a player needs a better key than their shirt number. The lineup table certainly shouldn't need one.

I wonder if you care about the order in which events occurred? If you do then you are counting on the event "id" to be a sequential number, which is a bad idea for several reasons. If you kept information about the time of the event you wouldn't need a synthetic key there either.

Synthetic keys only make the rows distinct. They don't detect/reject data errors (i.e. duplication); they conceal them. Errors don't go away because they are ignored.

There are well-known guidelines for choosing which attributes will be suitable keys. One is stability. Stability does not mean immutability (people make typing errors that have to be corrected for instance). If this is to be an SQL database then use ON UPDATE CASCADE when you declare your foreign key constraints.

-- 
Roy