This is a question purely about the conceptual design of a database. As soon as that's worked out I'll probably be fine. I'll do my best to present this in a clear way. Which is not that easy; if everything had been clear in my own mind, I probably wouldn't need your help. Please bear with me, I'm happy to clarify where needed

I am trying to design a database that will be used primarily for keeping track of individual results in a sports competition. Additionally, I want to be able to track down things like "since when has this person been playing?" and "how many tournaments has he been participating in?". The problematic part is that individual results in this case are really the sum of both team results and individual results (ie. every tournament not only has a winning team, but also a best player).

For example: a team made up of person A and B plays five rounds, wins all of them and wins the tournament. Person A has scored 50 points in every round, while person B has scored 60. This would add up to a grand total of 50 * 5 rounds + say, 100 bonus points for winning = 350 points for A and (similarly) 400 points for B. However, I do want to be able to keep track of how many points were acquired for being a good team and how many were won for being a good individual player.

To complicate things, although they will always be made up of two persons, teams are not fixed and may change every tournament.

Another possible constraint is the fact that I would like to use existing Excel-spreadsheets with tournament-results that are made up of two parts, namely:

1. the team-results (team name, individual A, individual B, rank)
2. the individual results (individual, result round 1, ... result round n, rank)

I have come up with an approach that basically is as follows (and partly inspired by the Athletes and Teams example of DatabaseAnswers):

Individual (
IndividualID,
Name (etc.)
)

Individual_Team (
IndividualID,
TeamID
)

Team (
TeamID,
TeamName
)

Individual_in_Tournament (
TeamID,
IndividualID
TournamentID,
TournamentName
)

TeamRank (
TeamID,
TournamentID,
Rank
)

On to my questions:

1. Is it indeed sensible to have the TeamRank linked to Team in this way for my purpose (I suppose it shouldn't be a problem to find the accorded teampoints for every individual in this way. Am I right?)?
2. When relating Individual_Team to Tournament, does it matter whether I link them through the IndividualIDs or the TeamIDs? I actually borrowed this part from DatabaseAnswers. I can see how the above solution may be more appropriate, but my own solution would have been not to relate these two tables, but add an extra junction table between Team and Tournament(TournamentID, Name) instead.
3. Most importantly: where do my individual results fit in? I have a few ideas, but I'm going to pose this as a completely open question. Individual results are comprised of an individual rank per tournament. This rank is derived at by calculating the average individual score for all the rounds in a tournament. I want to be able to do two things:
   • Give people who've done particularly well extra credits for having ended up in the top 10 of a certain tournament
   • Calculate a grand average over all rounds in all tournaments
   So the individual results must contain both information about the individual rank per tournament, and of all the points acquired in every single round of every tournament.

I kind of hoped it would lead to new ideas for myself, but it hasn't really. I hope having written all this will not turn out to have been in vain - so thank you for any suggestions, be it references to other threads/sites or advice in this particular case. They're most appreciated!

General rule of thumb - don't store any calculations...
Therefore I'd remove the rank table and simply work out these things as and when they are needed.

I.e. points can tell you the rank, rank cannot tell you the score. Store the points and everything should be just fine and dandy.

To answer questions such as
You need to record some dates somewhere, right? I don't see any in your design!

Another thing that strikes me here is - how do you know who played who in each tornament, and whether it was semi-finals, quarters, etc..? (assuming your tournaments work this way)

Enough for me - time to sleep - long day ahead

Merry Christmas

Thank you Georgev!

As for the "since when" question - that requires an extra row in the Tournament-table stating the year. I didn't include it to avoid cluttering up things.

To me it's not relevant who played who (it would be hard to find out, indeed), I'm merely interested in the results.

Thanks for pointing out that I do not have sufficient information to know who won the final rounds. I totally overlooked that. The available data are about preliminary rounds only. It is possible to deduce which teams made it to the semi-finals, but I assume I'll have to add the information about who eventually won the tournament manually.

I will try to draw up another concept using points only and no ranks (and something to make up for the lack of information about winning teams). I still don't quite see where that points-table would fit though. I am tempted to relate points to Individuals. But on second thought it might make more sense to relate them to Individuals_in_Teams, in order to be able to work out the team's rank based on the individual scores of the two teammembers.

That's just thinking aloud. Anyone's input on this or my previous post is still very welcome Happy holidays.

I would think that a transaction table would be the most effective way to accomplish all the things you need to do.

You would be able to track team and individual scores, rounds, bonus points. This would also allow you to do a large amount of differnt reporting on the information.

I don't know how many tournaments you run but lets just say if the is one every week thats 52 now there are 20 teams for each thats 1040 now there is 2 people per team so thats 2080 now you have say 10 rounds per tournament that means you would have at most 20800 records in that table each year. That amount is so small that any real database engine would be able to process that in a flash.

Just my 2 cents but that is what I would do.