Re: Database design for a little soccer league

From: Mariano C. <mariano.calandra_at_gmail.com>
Date: Mon, 21 Mar 2011 08:29:52 -0700 (PDT)
Message-ID: <aa917352-6841-4e08-bbd4-5d8cf6f3e81f_at_x18g2000yqe.googlegroups.com>


On 21 Mar, 09:29, Roy Hann <specia..._at_processed.almost.meat> wrote:
> 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

It's true. I don't need id key for lineups. No, shirt number isn't a key. ID key is an autoincrement table, and I have all those id column cause I need to call in that way. I'm using a framework, cakephp, and that naming convention is the better way. Yes, i use foreign key costraint.

My doubt was about associations type. Received on Mon Mar 21 2011 - 16:29:52 CET

Original text of this message