I'm working on a match scoring program, and one of the things where I'm hitting a snag is how to handle a specific aspect of the matches. One of those things that is very simple when done by hand, but I'm running into a wall trying to figure out how to express it in a database.

Basically... a typical event is a 'tournament' - may be one day, might be multiple days. I have a table just for storing the tournament information - name, description, start date, end date, etc.

Each tournament is comprised of one or more matches. Right now I have a table for storing the match information - a foreign key tying it to a particular tournament, the match #, the distance, # of shots, max possible score, and other pertinent information.

Finally, there is a table for scores... with an id primary key, and foreign keys to the Competitors table, the Matches table, and the Tournaments table, and fields for different aspects of the score.

The problem is... a 'match' may also be an aggregate of other matches. E.g. a 'tournament' may consist of 'matches' #1, 2, & 3 on Saturday, 'matches' #5, 6, & 7 on Sunday, and also 'match' #4 (Saturday agg), 'match' #8 (Sunday agg), and 'match' #9 (overall agg).

I'm not sure if there actually is a problem with the layout I have so far, or if I'll just have to handle certain matches being aggregates of others in the main program, rather than in the database itself?

TIA,

Monte

the only thing i can see that might be problematic is that you have a FK from the scores to the tournament, leading to the possibility that you might incorrectly link a score to a tournament for a match that doesn't belong to that tournament

other than that, i don't see anything to be concerned about

i did not understand your problem about matches being aggregated differently from the relationship of multiple matches in the same tournament

Yeah... that was looking like less and less of a good idea as I typed that... but it was what I had at the time so I figured I was best off being honest

I guess where I was having a hang-up was thinking that somewhere in the Scores table or the Matches table that I needed to store *which* matches a match is an aggregate of. It would most likely be part of the Description field in the Matches table... but I had envisioned that as being more for notes, etc.

I had thought of having a boolean 'Y/N' field named 'Agg' in the Matches table... but then I'd have to add a field with a string of match numbers in it, and a lot of records would have nothing in this field, which made me start wondering if it should somehow tie to another table... and how I'd express that?

how can a match be an aggregate of other matches?

i don't get it

It's just a common way of handling things administratively... as an example, Matches #1, 2 & 3 may be at 800, 900, & 1000yds respectively, each with a possible score of 150 points. Match #4 - the daily aggregate - would then have a total possible score of 450 points. Match #9 - the overall aggregate for the whole tournament - would consist of both daily aggs for a total of 900 possible points.

Depending on the size of the event, there may be awards for only the daily aggregate, or for each individual match. To make things even more fun, some multi-day events may have a 'match' consisting of the aggregate of just the 800yd matches, another 'match' consisting of the 900yd aggregate, and so on - in addition to the daily aggregate match and the overall aggregate match for the whole tournament.

There is one event in Chilliwack, BC that has a pretty good-sized matrix to help show which matches count in which aggregates - even with that its still a little confusing keeping track of which scores count in where.

Would it make more sense if I broke the scores down into 'stages' (another common way of referring to individual portions of an overall match), and made it so a 'match' could consist of one or more 'stages'?

Re-hashing the example above... there would be an 800yd stage, a 900yd stage, and a 1000yd stage for Saturday. Match #1 would be an agg of the 800yd 'stage' (i.e. just one stage), Match #2 the 900yd 'stage', Match #3 the 1000yd 'stage', Match #4 would be the agg of three 'stages', etc. and Match #9 would be the agg of six 'stages' (800/900/1000yds on both days).

So now I need to basically rename the 'Matches' table to 'Stages', edit the 'Scores' table to remove the FK from 'Tournaments' and change the FK from 'Matches' to a FK from 'Stages', and create a new 'Matches' table that would have a primary key 'id', and fields for 'MatchNumber', 'MatchName', and... a foreign key from 'Stages' tying it to a given stage. Then I would have to write a query to SELECT all the scores from stages in a given match... I think.

How's that sound?