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