Help with a Tennis Project

[Daniel Shanahan]

I'm trying to create a database to keep track of a tennis league. I have a Players table and a Matches table and set them up as a many-to-many relationship. The trick is, at least for me, that there are more than one players. The db will keep track of singles as well as doubles matches. In the Matches table I would like to create a portal that will list all of the players (two if a singles match and four if a doubles). That seems fairly straight forward - I can just add those names to the join table. However - and this is where I'm stuck - only one of those names will be connected to the Players table (via _fk__Player_id).

I don't think I am thinking about this correctly. I can't figure out how to have two or four players listed in the join table and have that information related to the player's individual record.

Does this make any sense? Any ideas?

Thanks.

dan

[mr_vodka]

Try using 3 tables. Players, Match, and a Join table in a many to many. Do a search for +Invoice or +"Join Table" and I am more that sure you would find some examples.

Each match would have multiple player entered in the join table which would then be related to the players table.

[Daniel Shanahan]

Thanks for your reply, Mr. Vodka.

I am using a join table. The problem is not getting the players to show up in the portal on the match field. The problem is auto-populating all the players' records when I assign a match to four players in the join table.

The join is connected to the Match table via fk_Match = pk_Match. This part is fine. The other end of the join is connected to the Player table via fk_Player = pk_Player. This is where I'm stuck. If I have four players (a "Doubles" match), the join is only related to one of them. For that one player record this is great because the portal on that record shows the related information from the join table. However, the portal on the record of the other three players is empty. This makes sense to me because I can only put one foreign key into the fk_Player field on the join table.

NEW THOUGHT...

This just occurred to me while typing: maybe the fk_Player field can have a mulit-key? I'll give that a try.

[mr_vodka]

The related player info fields would have to be in the portal. If you are trying to put it on a layout for the Match TO, it will only display the first related record.

If you must have it on the layout, you will have to rethink your design. If you have no match specific data for each player that you want to store, then you can skip the join table all together and you can have 4 fields to store the 4 playerIDs. Since there should never be more than 4 players per each match, relationally, you should be okay. IMHO, over-normalization can hurt you when its not pratical.

However, if you do have match specific player data, then you may still want to keep the join table and instead, use a global calc to store the string "Player1", "Player2", etc. You would then use a multi-predicate join keyed on Match and each of these global calc to the join table, MatchID and MatchPlayerID. So in the end you will have 4 new relationships. Example