Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    2

    Unanswered: Trouble with database design

    I am trying to make a sport database. It has to have both contests scheduled and then be updated with the winner or loser. The problem I have is figuring out how to do this without duplicate data. The schedule has two columns, one each for the players on each side. The winner table has a foreign key to hte schedule table and is supposed to have a column for the winner of the contest but the only constraint that would work is saying that it must exist in one of two columns, because the winner can be on two columns in the schedule table. I can't do this. I tried making a temporary table for the 'references' part of the constraint - a union of the schedule table, but sql doesn't allow that.

    Should I redesign the whole thing? I can't find any example schemas on the web that do what I want to do here.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Why is there any "winner table" at all?
    I would have thought that at most there would be a derived "winner" column in CONTESTS table.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2013
    Posts
    2
    Quote Originally Posted by anacedent View Post
    Why is there any "winner table" at all?
    I would have thought that at most there would be a derived "winner" column in CONTESTS table.
    I'm not sure if I get what you mean.

    I had a table that was called 'results', it had who won and some stats on the win, that's what I meant by the winner table. So you are saying I should just put everything in the schedule table and those two columns in the results table into a 'contests' table?

    Even if I do that doesn't that problem remain? How do I enforce that the winner is going to be either of the two player columns in the contests table?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    We speak SQL.
    Do you speak SQL?

    consider to actually post CREATE TABLE statements so there is some common ground upon which to base a conversation.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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