Parent-child relationship: multiple tables vs. self-join

Question

I'm working on a database for stat keeping for a sports club where I help administrate some events. I think I've got the parts for keeping track of the competitor names, classifications, categories, etc. figured out using a main table, several lookup tables and a couple bridge tables to cover the many-to-many relationships involved.

I have a model sketched out for the next part, but I'm not entirely comfortable that I'm doing it right. I know how we do it on paper and/or in a spreadsheet where I'm still doing a lot of manual work, but I'm not 100% confident I'm 'translating' it to tables and relations correctly.

A simple event structure might look like this:

Tournament  
- Day 1  
    - Match 1 (Fired Match)  
    - Match 2 (Fired Match)  
    - Match 3 (Fired Match)  
  - Match 4 (Aggregate Match of 1,2,3)  

- Day 2  
    - Match 5 (Fired Match)  
    - Match 6 (Fired Match)  
    - Match 7 (Fired Match)  
  - Match 8 (Aggregate Match of 5,6,7)  

 - Match 9 (Aggregate Match of 4,8)  

I had broken things down a bit further and have tables for 'scores', 'stages', 'matches', and 'tournaments' (among others). The idea being that scores are the raw data I need to record for each competitor, with foreign keys to the 'competitor' and 'stages' table. Rather than having 'fired' and 'aggregate' matches, I decided to approach it as matches being aggregates of one or more stages, and tournaments can have one or more matches (aggregates).

I left out some of the lookup tables indicated by the other foreign keys since they don't directly relate to the matter at hand which I guess is "Is this the correct way to go about this, or would I be better off to skip the matches/stages separation? Would there be a way to reimplement this parent-child relationship between matches and stages in a single table via a self-join?

TIA,

Monte

Answer 1

It looks like a properly normalized and flexible hierarchical structure to me.

As designed, it implies that a Tournament has one or more Matches, and a Match has one or more Stages... or, more to the point, each parent has a variable number of children, where the number is always greater than zero but can and will vary from instance to instance. If this is not the case, if a parent (Tournament or Match) will always have the same number of children, then there might be some minor advantage in denormalizing the data (like a table with seven columns for the weekdays) , but that may prove awkward in the long run.

I would definitely not do a single table with a self join, as the different levels of the hierarchy contain different attributes. For example, "Shots" is part of a Stage and not part of a Match, and so should not be made part of the Match table.