Edit : update a generic question to reflect the actual domain: hockey sport.
The actual event is the game schedule, and the participants are the teams.
Teams are the ultimate “owners” (that is, when a team is removed, so any planned games, results, players and player statistics associated with it).
The problem discussed so far in this thread covers the decision to combine the event in one row with two columns (team1, team2) or go to the connection table. The consensus so far is to stay using two columns. However, given that the original question is applicable to generic events, and not to planned games with related results, there may be a change in approach (for example, some may say that the schedule of the game should contain information about the BOTH schedule [date, time, location, teams] and information on the outcome of the game / results (score, win-loss-connection, game for penalty minutes, etc.), and therefore, to create unique game identifiers, you need to add a connection table).
The answers so far have been excellent; -) Mark as responding pending any updates. Thanks everyone!
ORIGINAL QUESTION:
Perplexed by how to solve this problem.
What is the normalized approach to handling an event (taking place on a given date and place), where there will always be exactly 2 participants?
The irregular approach is to create an event table:
1) eventID PK (autonum)
2) two columns, participant1 and participant2, PC (autonum) from the participants table.
Although this approach consolidates the creation of events in a single table record (there is no connection table for creating event identifiers), one of the problems with this design is that, technically, the participants must be the property of the side of the equation; that is, when a participant is deleted, any associated event must be deleted, since orphaned events are not allowed.
, , , , eventID , . joinID , . FK ID ( ) .
? , ( ), - ( ) (), , : -)