SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast hartmurmur's Avatar
    Join Date
    Aug 2001
    Location
    Chicago
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table/Relationship Structure Help Needed

    This is database non-specific, rather a request for help in creating the proper database structure. (I use FileMaker Pro for my local db and mySQL server for web).

    I would like to create a database that will track athletic competitions - games (football, basketball, baseball, whatever) between 2 teams. For this I have created several tables:

    Conference (one conference contains many teams)
    Teams (each team belongs to one conference)
    GameHistory
    Games (each game has many teams - 2 to be specific)

    So the basic structure would look like this (excuse the text ER diagram):
    Conference -> Teams -> GameHistory <- Games

    My question: How can I track each teams' opponents? Each row in the Games table will have 2 related rows in the GamesHistory table - one for each team competing store related info pertaining to the the game.

    I know I am missing something. Not interesting in doing anything with scheduling at this point. I know that will be a whole other hairy mess.

    Any help would be appreciated. Let me know if you have any questions since explaining a db structure can be tough without visuals.

  2. #2
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not use a structure like this? tida and tidb are the id's of both of the teams that played in a game in a given conference. gid is game id, cid is conference id, hid is history id, and so on....



    And then use MySQL joins to link them together when calling the data (represented by arrows above).
    Last edited by Aes; Sep 25, 2001 at 22:55.
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  3. #3
    SitePoint Enthusiast hartmurmur's Avatar
    Join Date
    Aug 2001
    Location
    Chicago
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aes,

    I don't think it is a good practice to put the same information in two separate fields as you have suggested with tida and tidb. The main problems it presents are:

    1.) How do you know who is supposed to be Team A and who is supposed to be Team B?

    2.) You have 2 foreign keys in one table that point back to a single primary key in the related table.

    I will post a gif of my E-R diagram in a short while.

    Thanks for your input!

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Table/Relationship Structure Help Needed

    Originally posted by hartmurmur

    Conference (one conference contains many teams)
    Teams (each team belongs to one conference)
    GameHistory
    Games (each game has many teams - 2 to be specific)

    So the basic structure would look like this (excuse the text ER diagram):
    Conference -> Teams -> GameHistory <- Games

    My question: How can I track each teams' opponents? Each row in the Games table will have 2 related rows in the GamesHistory table - one for each team competing store related info pertaining to the the game.
    If there is a 1:n relationship between Conference-Team, then you only need to do this:

    Team(id, conference, ...)
    [FK] confernece references Conference.id

    Confernece(id, ...)

    Now unless you are using a RDMS that can handle Extended-ER schema (like Oracle - which I know nothing about), then I would not differentiate between a game that has happened, to one that is scheduled to happen, etc, etc. I would be inclined (at least as a starting point) to say a game is a game is a game whether it is pending, underway, postponed, been-played, etc).

    Eg:

    Game(homeTeam, awayTeam, date, status, result, ...)
    [FK] homeTeam references Team.id
    [FK] awayTeam references Team.id

    Now, I am sure that normalisation issues will arise from Game. But it is a starting point to work forward from (perhaps ). For example, result is dependent on status (can't have a result until status is that the game was completed) so there is a transative dependency problem there. But anyway, I'm just making it up - only you know what needs to be modelled.

    Maybe you need other tables:

    GameStats
    TeamGameStats(gameID, teamID, ...)
    [FK]gameID references Game.id
    [FK]teamID references Team.id
    Additional constraint: TeamGameStats.teamid = game.homeTeam or game.awayTeam
    Additional constraint Game.status must be "complete"

    blah - Its tough isn't it - I don't think I'm doing a very good job here. Best I shut up now

  5. #5
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    tida and tidb would both be id numbers of teams; you could get which one won or lost a given game in the conference from the history/game tables; the reason I set it up that way ... I don't see why that wouldn't work well....

    Regardless, good luck with what scheme you come up with.
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  6. #6
    SitePoint Enthusiast hartmurmur's Avatar
    Join Date
    Aug 2001
    Location
    Chicago
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aes (and freakysid),

    I understand your methodology. I mentioned in the original post that there would be 2 teams per game. If the number of teams never was more than 2 I would agree that it may be suitable to design in this way. However, it is still a many-to-one relationship (and even only 2 teams are considered many).

    I intentionaly didn't say what types of games I would be tracking because it is too easy to assume there are always 2 teams involved. Let's, for example, say the game was cards. Let's also say that instead of 2 teams per game, we had 10 (or 20 or 30) teams. Following this design you then have fields for tida, tidb, tidc, tidc...tidj? See where the design gets flawed?

    Since this is a prjoect that I have 2 more months to complete, I will continue working on it and will keep you posted on my design. Any other suggestions welcome!

    Thanks!

    Originally posted by Aes
    tida and tidb would both be id numbers of teams; you could get which one won or lost a given game in the conference from the history/game tables; the reason I set it up that way ... I don't see why that wouldn't work well....

    Regardless, good luck with what scheme you come up with.
    Last edited by hartmurmur; Sep 27, 2001 at 09:20.


Bookmarks

Posting Permissions

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