How to manage multiple fields related to the same field

[Chappy.Cole]

So, I have been working on a volleyball tournament database. In each match, there are 3 teams involved: 1) Home team ; 2) Away team ; 3) Referee team. Each team in any match will at some point be performing each of the roles, i.e. every team is sometimes the Home team, sometimes the Away team and sometimes the Referee team.

My database has a Team table and a Match table. When I am designing a layout to show a summary of a match, I have the need to display information about each of the 3 teams. I know that I can achieve this by having 3 TO's of the Team table, each related to the Match table to the 3 team roles. I have no technical problem with this.

However, when the tournament gets to bracket play, I have 12 teams in a single Bracket table record (it could be much more, e.g. 32). Again, I know that I can solve my requirements with 12 TO's of the Team table.

My question is:

Is there any other, less complex, less bothersome way to manage this relationship issue?

Consider that each team in a match or bracket does have a distinct designation, i.e. it is not just another team. I don't believe that a join table will solve my problem...Hmmm...

Well, after a little testing I guess a join table might work by putting the attributes that distinguish one team from another within a match, in the join table. So, my tables would be:

1) Team: contains Team_Id, Team_Name, etc.

2) Match: contains Match_Id, Court_Number, etc.

3) Teams_In_Match: contains fk_Team_Id, fk_Match_Id, Team_Role

where 'Team_Role' would hold 'Away', 'Home' or 'Referee'.

Hey, experts (Comment, are you watching), does that sound like the right (i.e. a sound) approach?

Thanks in advance

[comment]

when the tournament gets to bracket play, I have 12 teams in a single Bracket table record

I believe this is a mistake: a match within a bracket is still a match, with one home team, one away team and one referee team - not a mêlée of 12 teams.

Re a TeamRoles join table:

It is certainly a legitimate approach. However, with only three roles per match, and taking into account that there MUST be exactly the SAME three roles for each match, I cannot really find fault with your current method of attaching 3 TO's of Teams to a Match.

[Chappy.Cole]

I probably am not explaining myself thoroughly. My database represents a season of play. The sequence of tables is:

-> Events : contains a list of the major events within a season

-> Tournaments: contains a list of the individual tournaments, which involves 12 teams (out of hundreds). Altogether these tournaments comprise one season event

-> Pools: contains information about the pool-play section of a tournament which is quite different from bracket-play, although both are composed of matches

-> Brackets: contains the information about the playoff brackets at the end of the tournaments

-> Matches: contains the details of every individual match, both pool matches and bracket matches

Also in my database, but not part of the "is composed of" sequence above, are:

-> Teams: contains the details of each team in the league - noteworthy is that there is an Age Group field, which is somewhat of a "composed of" factor, i.e. the set of Teams is composed of Age Groups and every tournament is for 1 specific age group.

-> Team Seed History: contains the seed number that each team owns before and after each event.

Perhaps that was too much information, but I was making the point that my issue regarding the '12 teams in a bracket' was separate from, but similar to the '3 teams in a match' issue. I would like to be able to create layouts which provide information about a bracket. As I said in my previous post, I know that I can achieve my goals with 12 TO's of Teams, but that seems very messy. As an FMP tinkerer (i.e. I currently don't make a living as an FMP developer), but also a Software Engineer, when I feel that something is messy, it makes me suspicious that my solution contains a design flaw.

How would you handle the '12 teams in a match' issue?

[comment]

Perhaps that was too much information

I am afraid that may be so. I still don't see a '12 teams in a match' situation anywhere. It's been a while, but I don't recall the rules of volleyball allowing 12 teams on the field at the same time.

It seems to me a match is a match - whether played within the pool-play or the bracket-play section of a tournament. A bracket can have matches as children, and the teams playing in those matches as grandchildren.