Teams, fixtures, results (1 Viewer)

[Rob Stir]

Hi, Ive been away from access for 8yrs. Coming back trying to design darts league. I have tables
Division: divID, divName
Team: teamID, teamName, divID, fixtureID
Players: playerID, firstBame, lastName, teamID, active(y/n)
Fixtures: fixtureID, fixtureDate, homeTeamID, awayTeamID
1 to many joins: Division-Team-Players
Which work fine.
I've put in some fixture numbers and can display them ok but can tie to any results in a 1 to many table even with a junction table.
I've scoured the web, been back to basics with tutorials from Richard Rost but a lost.
Any pointers? Nothing of use anywhere about sport, soccer, football etc. All concentrate on order db's.
Thanks in advance

 

[The_Doc_Man]

We actually have had some league DBs - try searching for Golf or Soccer or Tennis in this forum. Also, don't ignore the links in the "Similar Threads" list, which includes three football league threads.

Looking at your tables, I'm confused because I don't know what a "fixture" means. (I don't play or follow darts as a sport.) Is that like a match or a tournament? Depending on what formal relationships were declared, I wonder if there is a risk of having a circular reference between Fixtures and Teams, both of which contain both fixtureID and teamID. If there is no formal relationship, not a biggie. However, if there IS a formal declared relationship involved, the query wizards might get a little bonkers when they consult the relationships table.

 

[Rob Stir]

We actually have had some league DBs - try searching for Golf or Soccer or Tennis in this forum. Also, don't ignore the links in the "Similar Threads" list, which includes three football league threads.

Looking at your tables, I'm confused because I don't know what a "fixture" means. (I don't play or follow darts as a sport.) Is that like a match or a tournament? Depending on what formal relationships were declared, I wonder if there is a risk of having a circular reference between Fixtures and Teams, both of which contain both fixtureID and teamID. If there is no formal relationship, not a biggie. However, if there IS a formal declared relationship involved, the query wizards might get a little bonkers when they consult the relationships table.

Thanks for this, I'll have a look around.
A fixture is the match between the home and away teams. The team table has a fixtureID which I thought would be a good foreign key for a relationship but I can't get it to work even with a junction table.
Rob

 

[LarryE]

Hi, Ive been away from access for 8yrs. Coming back trying to design darts league. I have tables
Division: divID, divName
Team: teamID, teamName, divID, fixtureID
Players: playerID, firstBame, lastName, teamID, active(y/n)
Fixtures: fixtureID, fixtureDate, homeTeamID, awayTeamID
1 to many joins: Division-Team-Players
Which work fine.
I've put in some fixture numbers and can display them ok but can tie to any results in a 1 to many table even with a junction table.
I've scoured the web, been back to basics with tutorials from Richard Rost but a lost.
Any pointers? Nothing of use anywhere about sport, soccer, football etc. All concentrate on order db's.
Thanks in advance

You have:

1. Multiple Divisions
2. With multiple Teams
3. With multiple Players
4. With multiple Fixtures
5. With multiple scores

but you have no relationship between Players and Fixtures, so the Fixtures table needs a PlayerID field as a foreign key with the Players table and there needs to be a Scores table so each Player on each team participating in each Fixture records a score. The Scores table has a ScoreID as a primary key and FixtureID as a foreign key.

Next, create a 1-1 relationship between the tables with Referencial Integity enforced:

FixtureID in the Team table is not needed.

1. Division and Team
2. Team and Players
3. Players and Fixtures
4. Fixtures and Scores

 

[Rob Stir]

You have:

1. Multiple Divisions
2. With multiple Teams
3. With multiple Players
4. With multiple Fixtures
5. With multiple scores

but you have no relationship between Players and Fixtures, so the Fixtures table needs a PlayerID field as a foreign key with the Players table and there needs to be a Scores table so each Player on each team participating in each Fixture records a score. The Scores table has a ScoreID as a primary key and FixtureID as a foreign key.

Next, create a 1-1 relationship between the tables with Referencial Integity enforced:

1. Division and Team
2. Team and Players
3. Players and Fixtures
4. Fixtures and Scores

Thanks for this, I'll give it a go in the morning

 

[XPS35]

A fixture is the match between the home and away teams. The team table has a fixtureID

In that way a team can only be involved in ONE fixture. That is not what you want.
A fixture only should hold two foreign keys to team: home team and away team (as you seem to have). No junction table needed. No fixtureID in team.

 

[XPS35]

1 to many joins: Division-Team-Players

And what if a team is promoted or relegated to another division?
Or a player transfers to another team?
Isn't history interesting?

 

[Rob Stir]

And what if a team is promoted or relegated to another division?
Or a player transfers to another team?
Isn't history interesting?

Oh yes. I hadn't thought about that bit.

 

[LarryE]

After further consideration and review, a table and relationship schema like this may meet your needs where:

1. Each division has several teams
2. Each team has several players
3. Each player has a score for each Fixture in TblResults. Each new entry in TblResults requires a PlayerID and FixtureID.
4. The Home and Away teams are simply a field for each Fixture since each team may be a Home team or an Away team for any given Fixture.

Of course you can add any other fields you require for each table. I noticed you have an "Active" field but wasn't sure what "active" meant.

 

[Rob Stir]

After further consideration and review, a table and relationship schema like this may meet your needs where:

1. Each division has several teams
2. Each team has several players
3. Each player has a score for each Fixture in TblResults. Each new entry in TblResults requires a PlayerID and FixtureID.
4. The Home and Away teams are simply a field for each Fixture since each team may be a Home team or an Away team for any given Fixture.

View attachment 106664
Of course you can add any other fields you require for each table. I noticed you have an "Active" field but wasn't sure what "active" meant.

Thanks Larry, I'll give it a go.
I put 'active' to show they're still wanting to play that particular season

 

[Rob Stir]

After further consideration and review, a table and relationship schema like this may meet your needs where:

1. Each division has several teams
2. Each team has several players
3. Each player has a score for each Fixture in TblResults. Each new entry in TblResults requires a PlayerID and FixtureID.
4. The Home and Away teams are simply a field for each Fixture since each team may be a Home team or an Away team for any given Fixture.

View attachment 106664
Of course you can add any other fields you require for each table. I noticed you have an "Active" field but wasn't sure what "active" meant.

This is really good. Many thanks

 

[LarryE]

If some players are "active" some seasons and some not, then we have a whole new scenario. You are going to need a TblSeasons table to track which players are active which seasons.

So for each division you have teams
For each team you have Seasons
For each Season you have Players
Each Player is Acive or not for each Season.

Make SURE you change the ID names and relationships in each table to reflect the new scenario.

 

[XPS35]

View attachment 106664

As this is about darts I assume a fixture consists of a number of matches between 2 players of each team. A "result" for one player doesn't make sense in that scenario.
The OP doesn't mention individual games or anything like that. He only seems to be interested in the total result of al the games of a fixture. So fixture should be related to team twice and something like games won by home team and games won by away team.

 

[Rob Stir]

If some players are "active" some seasons and some not, then we have a whole new scenario. You are going to need a TblSeasons table to track which players are active which seasons.
View attachment 106667
So for each division you have teams
For each team you have Seasons
For each Season you have Players
Each Player is Acive or not for each Season.

Make SURE you change the ID names and relationships in each table to reflect the new scenario.

ah, yes. I actually had the seasons table in an earlier version I'll put it back in.
Thanks

 

[Rob Stir]

If some players are "active" some seasons and some not, then we have a whole new scenario. You are going to need a TblSeasons table to track which players are active which seasons.
View attachment 106667
So for each division you have teams
For each team you have Seasons
For each Season you have Players
Each Player is Acive or not for each Season.

Make SURE you change the ID names and relationships in each table to reflect the new scenario.

isn't season/player many to many. I can't get it to work at all

 

[LarryE]

This schema seemed to work when I entered some test data

Each season contains divisions, teams etc.
Also, in researching darts fixture scoring, it appears to me that each player does indeed record a score in each fixture. Isn't that correct?

 

[XPS35]

This schema seemed to work when I entered some test data
View attachment 106676

No doubt it "works". But that doesn't mean it's a schema that accurately reflects reality. See my remarks on "results".

Plus, you don't want to enter your teams every season, as this model requires. A team can participate for several seasons, possibly in different divisions.

 

[Rob Stir]

This schema seemed to work when I entered some test data
View attachment 106676
Each season contains divisions, teams etc.
Also, in researching darts fixture scoring, it appears to me that each player does indeed record a score in each fixture. Isn't that correct?

you're right. I thought the division ID would ripple through but of course it's there to account for promotion/relegation as originally intended.
Wow, I've forgotten more than I thought.
I think the thought process itself is a trifle rusty.....

 

[LarryE]

Doing a little more research, does each "Fixture" consist of a certain number of "games" or "matches" or "legs"? Does each player play an opponent in a "game" or a "match" or a "leg"? And how does scoring work? These are all things that need to be addressed as well. As you can see, this could get complicated. I know little of this subject, I was just going on what you have told us. I think you need to really think about "Fixtures", "games", matches" and "legs" and what the hierarcy is. Only then, can you begin to develop a real model for tracking these things. ACCESS is very good at tracking stuff, but you better get the tables and realationships correct from the beginning or you will have nothing but problems.

I would take pencil and paper and draw a hierarcy picture of all the parts you need including Seasons, Divisions, Teams, Players, Fixtures, Games, Matches and Legs. Then you can build your model in ACCESS from there. It this point, we are just guessing.

 

[XPS35]

I am thinking about a model like this: