Trying to create an NFL database that has multiple tables but can't find a solution to creating an effective primary key

Question

The three tables I have currently are games, record, and teams. The teams table is exactly what you would think - a table full of all 32 teams with an id associated with them. The games table contains a games_id, which team was home and away, the week of the game and so on. The record table contains a record_id, the home and away team again, as well as a winner column.

The problem I keep running in to is finding a solution that would allow each team to be in the games table more than once and how to link that to the other tables. For example, the Patriots have an id of 21 in the teams table. if I put 27 as the id in the games table, I can only have the Patriots in their once, when they play 16 games...

Any suggestions are greatly appreciated and thank you in advance!

Answer 1

To extend other answers, relationships of sets underlie relational databases. A team can have one-to-many games. Therefore, a unique set of teams (team ID as primary key) should be related to many games (team ID as foreign key, allowed to repeat for each unique game instance). And because there are and will only be two teams per game, you will maintain two foreign keys pointing to same primary key.

To illustrate with a diagram:

Similarly, you can think of it as parent-child relationship. Games cannot exist without teams (i.e., child) and hence they rely on an external (or foreign) relation but teams can exist without games (i.e., parent) so rely on internal (pr primary) relation.

Answer 2

Expanding Justsalt answer:

Your table games need their own PK, I suggest game_id with auto numeric. A game is unique because is played on a specific stadium at specific date. In that game you will have away_team_id and home_team_id both are foreign keys to your table teams.

Answer 3

Look up the difference between primary keys and foreign keys.

In the teams table the Patriots have a primary key of 21. No other team has this key. In the games table a given game would have its own primary key (say 27), but in the 'home' and 'away' fields will be the keys for the two teams. In the context of the games table these are foreign keys, since they are keys not to this table (games), but to another table, teams. And there is no problem to use the same team key in any number of game records.

I might also probably combine your 'record' and 'games' tables. Put score/result fields in the games table and update them once the game has been played.