Table/Relationship Structure Help Needed

[hartmurmur]

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.

[Aes]

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).

[hartmurmur]

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!

[freakysid]

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

[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.

[hartmurmur]

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.