Results 1 to 5 of 5

Thread: DB Design

  1. #1
    Join Date
    Apr 2003
    Posts
    114

    Unanswered: DB Design

    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.

    Thanks for your help!!

  2. #2
    Join Date
    May 2003
    Posts
    23
    I can think of 2 ways.

    One, add a column in the PartData table, which would be where this participant finished this race.

    Two, a separate table, with a foreign key of PartID. From the PartData table you can get the RaceID, and from the RaceData table, you can get the EventID.

    Is this what you mean by "do I tie it back to the RaceData": A separate table, with 2 foreign keys, one of RaceID and one of PartID? But this is not normalized.

    Why not just put the finish position in the PartData table (One above)?

    Hope this makes sense.

  3. #3
    Join Date
    Apr 2003
    Posts
    114
    Makes a ton of sense but I am trying to adhere to the convention that there should be no duplication of data? Comment if you get a chance.

    I am beginning to think that I should tie Ind Results back to Participants and use a join to connect back to the Race Data table. Again, your comments would be appreciated.

  4. #4
    Join Date
    May 2003
    Posts
    23
    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).

  5. #5
    Join Date
    Apr 2003
    Posts
    114
    Thanks! It is nice to be able to bounce things off of people who know what the he** they are doing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •