Complex Relationships (2000)

[waveylines]

I am designing a database to capture the history of a sports league. The league currently has 3 divisions but this has changed from season to season. The number of teams in each division has also varied from season to season. Obviously, there are numerous fixtures to capture. How should I design the relationships ?
One season has many divisions but one division appears in many seasons. One division has many teams and teams have different divisions each season. A fixture is a match between two teams. Is it better to set up a 'one division to many fixtures' table or a 'one team to many fixtures'

Thanks for any help

[HansV]

You have a many-to-many relationship between seasons and divisions, so you need an intermediate table with season-division combinations, linked to the seasons table and to the divisions table.
You also have a many-to-many relationship between seasons/divisions and teams, so you need an intermediate table with season/division - team combinations, linked to the season-division table and to the teams table.
The fixtures table should be linked to twice to the season/division/team table, once for the home team and once for the visiting team.

[waveylines]

Thanks Hans.
I thought along similar lines but I thought all the intermediate tables was making it much more difficult than it needed to be.

[HansV]

It may seem complex, but you need intermediate tables to implement many-to-many relationships. See any good book on database design in Access, or take a look at ACC2000: "Understanding Relational Database Design" Document Available in Download Center.

[waveylines]

Thanks Hans. I'll download this file and take a close look. But setting the database up in this way would seem to pose another problem.

I wanted to use a form to enter current data. I thought the best way was with linked forms and subforms. I would use a form to select the current season, a subform to select the relevant division and another subform to choose a fixture by selecting teams from a drop-down list. However, I now get a very long list of teams.

As the fixtures table picks up the correct season and division by linking through the Intermediate table "Teams/Seasons/Divisions" rather than the "Teams" table, there are numerous entries:-

E.g.

ID Team Season Division
1 Blues 2005 1
2 Blues 2004 1
3 Blues 2003 2

If you want to enter future results, what would be the best apprach to limit this history of data? Ideally, I would want to be able to set the 'Season' on the form, by default, and then the choice of division would limit the dropdown list to only the relevant teams.

[HansV]

See MSKB articles ACC2000: How to Synchronize Two Combo Boxes on a Form and ACC2000: How to Create Synchronized Combo Boxes, or do a search for cascading combo boxes in this forum.