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

Mariano C. wrote:

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

On 21 Mar, 09:29, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:
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.

Mariano C. wrote:

If you are going to let half-wit frameworks design/compromise your
database you probably aren't going to get much love here.

That's fine, but my main point was that you can declare the FK so that
it is updated automatically when the referenced value is updated. The
(perfectly valid) wish to avoid having to write application code to
handle that situation is frequently cited as the justification for
"needing" spurious synthetic keys.

My secondary point is that using synthetic keys exclusively, without
also declaring any natural keys creates a data integrity problem
that will remain concealed from the applications or the DBMS. It's
akin to replacing a circuit breaker with a copper pipe. The electricity
will flow just fine...

I didn't even get to the point of thinking about that. I'm sure it
will be the least of the problems with the design.

--
Roy

On Mar 21, 3:53*am, "Mariano C." <mariano.calan... (AT) gmail (DOT) com> wrote:
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.

VTR250 wrote:

Yeah, we never even got to the bit about understanding the business
model. I tend to assume it's understood that I can't guess what fact
types the database must represent.

--
Roy

On Mar 22, 11:05*am, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:
There's that magic phrase again 'Business Model'. Mariano, will you
be recording whether the players have paid a subscription or is the
money up to the teams' managers? Alternatively, If the team has to
give you money to be in the league then whether they're paid up could
be an attribute of team (more likely the database will exist for more
than one year, so you need team_history to record payments and, if the
money can vary over time, fee_history). This is in addition to
player_history used to record their career -- useful if anybody ever
gets a medal -- discussed above.

This is only my opinion not a consensus: I suggest this has been a
useful first cut which has uncovered some (not all) problems and you
have now got to the point where you need to set out the things that
drive the design, then make a fresh start on the data model (you have
made a good start and expect some radical changes to the design).
Things that drive the design are: you need an outline business model,
some examples of what you want the system to do (you can generate a
league table with the information you have but I'm not sure about
fixtures*) and the rules that govern the data (eg. "a player can be
loaned from one team to another team for a match if they don't have
enough players" etc.). Also suggest you think about other stakeholders
that use this information and what do they need (eg. parents need to
know fixtures, subs) + are there any other stakeholders (will a profit
be used to buy equipment? I assume all of this is tax exempt.)

* for fixtures there are some special teams QF1 winner, SF2 winner
etc. how does that affect your FK in Matches?

Mariano C. wrote:

Mariano,

It's not possible to do database design over usenet. Anyone offering
detailed design decisions doesn't know what they are talking about.

It's a little league database. Nobody is going to die if you make a
mistake, and I am sure you will make plenty. I suggest you make them and
then see what's difficult to do and what's easy to do with your design.

As long as you don't plan to jump from the little league to something
that could do real damage, I see little harm.

If, on the other hand, you plan to make a career out of data management,
I suggest a solid education in the fundamentals of data management first.