Re: Database design for a little soccer league

On Mar 22, 11:05 am, Roy Hann <specia..._at_processed.almost.meat> wrote:
> VTR250 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.
>
> 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

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?