Relationships

[Ray Pearson]

I am trying to create a db for my local bowling club and so far I've got -

tblBowlerDetails, ID, Surname, firstName etc etc

tbl Matches BowlerID, MatchDate, Venue, Team, Game, ClubPlayed.

The relationship between the two tables above is obviously one to many.

There maybe four Teams playing on any given day with up to four bowlers per team. OK so far??

After the games are played I would like to record whether the teams won or lost their respective games. Can anyone throw any light on this one Please?

I am assuming that this is a many to one relationship but how do achieve the end result?

Thanks in advance - Ray

[mal....@gmail.com]

Ray,
One option would be to have an ID field in the Matches Table. "tblMatchesWonBy" - and store the ID of the winning team there. However it might be better to store each person/teams score...and just calculate the winning team through a query (Whoever has the highest score)
Or perhaps you need that at the GAME level....which might be then storing the PersonID - and handy to track their personal stats if/when they move teams....and still able to total a team of players for overall team winner.
In that case you would need a tblTeams. which can have names, captain (personID), etc.

Hope that helps.
Mal.

[Keith Tizzard]

I would have the following tables:
Bowlers as you have it
Matches with match date, venue, opponents
Teams with a name

then I would have some linking tables
TeamBowlers with TeamID and BowlerID i.e. who is playing in each team
MatchTeams with MatchID and TeamID; and their result i.e. which teams are playing in each match

I am assuming that a team of, say four players, can play in a number of matches. If one or more member changes it becomes a new team.
Over a time a Bowler could be a member of a number of Teams. You would need to take care that when selecting which teams are to play in a given match no Bowler is the member of more than one team in that match.

Of course I may change these when I develop it but I would start here

Hope this helps

Jim

[Ray Pearson]

Thanks to Mal and Jim for your suggestions. Now I have a starting point.

Thanks again - Ray