I am having trouble making a decision about my DB Structure. Here is the question:
My database contains an EventData table that catalogs fitness events (primary key EventID). Each event has multiple races, so I have a RaceData table (foreign key EventID, primary key RaceID). Each race has many participants, hence a PartData table (they are participants in the races of the events so I have foreign key RaceID and primary key PartID). Feel free to comment on the structure so far. Here is the dilemma I am having:
I also have a Results table to chart results of each race. Do I tie this back to the PartData table (foreign key PartID) because it essentially keeps track of the places of the participants, or do I tie it back to the RaceData (foreign key RaceID) because it tracks finishers of races? I could see where it would be helpful to do it either way.
There are times when it is helpful to get the race id directly from the results table but it seems to me that it is more solid to trace finishers back to the participants list.
I am assuming that it is not a good idea to do it both ways, right? I tried that and I had a hard time setting relationships.
If I understand your last comment, I think I agree. )
Which is tie Results to Participants, and join to Races. That makes sense to me.
No matter what, Results needs a foreign key to Participants (unless you just put the results data directly into the Participants table).