I have 2 database tables: Teams and Games.
For the purpose of this question, we are dealing with football (soccer) teams and games.
Each Game has exactly 2 teams, generally a home team and an away team although occasionally both teams can be neutral.
My question is whether I should represent this data relationship using 2 foreign keys in the Games table (home_team_id, away_team_id) or whether I should use a many-to-many relationship with a games_teams table to link the two, in which case I would need to also store whether the team was the home or away team and seems a little overkill.
To add to the confusion, I am using the ORM libs in KohanaPHP and these would expect an fk to be called team_id or a link table to contain only 2 columns. If you have experience with this problem in KohanaPHP then please leave a reply, else any general advice is also much appreciated.
If you want to be able to pin a "Xth Normal Form" badge on your database server, then it should probably be treated as many-to-many, otherwise, I should think you'll reduce your query overheads with 1 fewer table that you're just going to join through every time you want some useful data out.