Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2007
    Posts
    5

    Unanswered: Relating One Field to Two?

    hey guys
    i posted in another thread and got some good advice that led me to research in the right direction. I have one more question. Im am trying to design a database that tracks college football statistics. I will include a screenshot of my current design for reference. i have a table 'tblTeam' that just lists all the teams in the league with an id. My problem comes when i try to relate this to the table where I will be storing all the game info, 'tblGame'. obviously, per game record, there will be two teams. So my question is, does it make sense, or good design, to relate one field from a table to two fields in another? Access let me create the basic relationship, but would not allow me to click the option to "Enforce Referential Integrity".
    Attached Thumbnails Attached Thumbnails football.JPG  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it makes good sense to relate team1 to team and team2 to team too

    might be easier if you weren't using ms access

    by the way, i would remove the teamconferenceID column column from the tblTeamConference table table, make (teamID,conferenceID) a composite PK, and move the yearstart and yearend column columns into the tblTeamConference table table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2007
    Posts
    5
    Quote Originally Posted by r937
    yes, it makes good sense to relate team1 to team and team2 to team too
    i kinda thought so, but does it matter that i cant enforce the integrity? i will eventually be tying this into a c# program with lots of queries, so does that matter?

    Quote Originally Posted by r937
    might be easier if you weren't using ms access
    im open to use whatever kind of database is best. i chose access for two reasons. 1) it lended itself well to .NET and 2) i needed a db that could act as a stand alone file in a single user desktop program, without the need for extra software to be installed or a server, etc

    Quote Originally Posted by r937
    by the way, i would remove the teamconferenceID column column from the tblTeamConference table table, make (teamID,conferenceID) a composite PK, and move the yearstart and yearend column columns into the tblTeamConference table table
    i thought about that too. im sure i can combine those tables easily, but in my research i read that data seperation is good too...
    Last edited by Rv5; 02-09-07 at 04:08.

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    but in my research i read that data seperation is good too...
    Data separation is good, but only if there is a good reason for it. In this case, it is a one to one relationship, so the question would be, what good is accomplished by separating this data from the actual key. The key being TeamID and ConferenceID.

  5. #5
    Join Date
    Feb 2007
    Posts
    5
    ok good to know. ill combine those two into one. so is the fact that i cant use the access option Enforce Referential Integrity not a real big deal?

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I'm not sure why Access wouldn't give you that option, but separating the data into two tables, when there is no good reason to do so was something I could address. And, putting the two tables into one makes the need for the referential integrity issue go away. I thought that was a really good deal.

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Rv5

    If you add tbTeams to the Relationship view a SECOND time (this will be named tblTeams_1) you should be able to define a referential integrity relationship between this new table and team2 in tblGames.

    ??

    HTH


    MTB

Posting Permissions

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