Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complex Relationships (2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complex Relationships (2000)

    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.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Relationships (2000)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complex Relationships (2000)

    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.

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Relationships (2000)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complex Relationships (2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •