Database Design :: Normalization in 2 Participant Event :: Join Table or 2 Column?

Question

Edit: updating generalized question to reflect actual domain: sport of hockey.

The actual event then is a games schedule, and the participants are teams.

Teams are the ultimate "owners" (i.e. when team deleted so should any related scheduled games, results, players, and player stats).

The problem discussed so far in this thread covers the decision to combine an event into a single row with 2 columns (team1, team2) or to break out into a join table. The consensus so far is: stay with 2 column approach. However, given that the original question applied to generic events and not scheduled games WITH related results, there may be a change in the approach (for example, some might say that the game schedule should record BOTH schedule info [date, time, location, teams] and game outcome/result info (score, win-loss-tie, penalty minutes power plays, etc.) and therefore a join table should be added to generate unique gameIDs).

Responses so far have been excellent ;--) Will flag as answered pending any updates. Thanks everyone!

ORIGINAL QUESTION:

---

Puzzled over how to solve this problem.

What is the normalized approach to handling an event (taking place on given date & location) where there will always be exactly 2 participants?

The non-normalized approach would be to create an events table:

1) eventID PK (autonum)
2) two columns, participant1 & participant2, PKs (autonum) from a participants table.

While this approach does consolidate event creation in a single table record (no join table to create eventIDs), one problem with this design is that technically participants should be the owning side of the equation; i.e. when deleting a participant, any related event should be deleted since orphaned events are not allowed.

The alternative, a join table, would as I see it generate a unique eventID along with date, time, and location. The revised events table would then consist of the join table eventID and 2 separate rows, one for each participant. With this approach I can easily add an FK to the events table for participantID (PK from participants table) and thereby have proper constraints in place.

How would you approach this problem? I should point out that we have been using the non-normalized design in production for a couple of years without issue (data constraints pushed to code level), but we are looking re-architect from the ground (Database) up (Code), thus the question ;--)

---

Answer 1

Why do you think that's not normalised? Both participant IDs depend on the event in a way that makes it 3NF (depending on the key, the whole key and nothing but the key, so help me, Codd).

However, if the date and location also depend on the event ID, they too should be placed in this table (with perhaps the location being a foreign key lookup to another table, similar to the participants). Something akin to:

Events:
    EventId           primary key
    Date
    LocationId        references Locations(LocationId)
    ParticipantOneId  references Participants(ParticipantId)
    ParticipantTwoId  references Participants(ParticipantId)
Locations:
    LocationId        primary key
    <<Other location stuff>>
Participants:
    ParticipantId        primary key
    <<Other participant stuff>>

Just because you have something that looks like an array, that doesn't automatically violate 3NF, it's just a warning sign that should be looked at.

Now, if you ever discovered an event that could have 0, 1, 3 or greater participants, then you'd want to rework the schema.

Until then, YAGNI.