Schema Normalization :: Composite Game Schedule Constrained by Team

Related to the original generalized version of the problem:http://stackoverflow.com/questions/6068635/database-design-normalization-in-2-participant-event-join-table-or-2-column

As you'll see in the above thread, a game (event) is defined as exactly 2 teams (participants) playing each other on a given date (no teams play each other more than once in a day).

In our case we decided to go with a single composite schedule table with gameID PK, 2 columns for the teams (call them team1 & team2) and game date, time & location columns. Additionally, since two teams + date must be unique, we define a unique key on these combined fields. Separately we have a teams table with teamID PK related to schedule table columns team1 & team2 via FK.

This model works fine for us, but what I did not post in above thread is the relationship between scheduled games and results, as well as handling each team's "version" of the scheduled game (i.e. any notes team1 or team2 want to include, like, "this is a scrimmage against a non-divisional opponent and will not count in the league standings").

Our current table model is:

Teams > Composite Schedule > Results > Stats (tables for scoring & defense)

Teams > Players

Teams > Team Schedule*

*hack to handle notes issue and allow for TBD/TBA games where opponent, date, and/or location may not be known at time of schedule submission.

I can't help but think we can consolidate this model. For example, is there really a need for a separate results table? Couldn't the composite schedule be BOTH the schedule and the game result? This is where a join table could come into play.

Join table would effectively be a gameID generator consisting of:

gameID (PK)

gameDate

gameTime

location

Then revised composite schedule/results would be:

id (PK)

teamID (FK to teams table)

gameID (FK to join table)

gameType (scrimmage, tournament, playoff)

score (i.e. number of goals)

penalties

powerplays

outcome (win-loss-tie)

notes (team's version of the game)

Thoughts appreciated, has been tricky trying t开发者_StackOverflowo drilldown to the central issue (thus original question above)

---

I don't see any reason to have separate tables for the schedule and results. However, I would move "gameType" to the Games table, otherwise you're storing the same value twice. I'd also consider adding the teamIDs to the Games table. This will serve two purposes: it will allow you to easily distinguish between home and away teams and it will make writing a query that returns both teams' data on the same row significantly easier.

Games
   gameID       (PK)
   gameDate
   gameTime
   homeTeamID
   awayTeamID
   location
   gameType     (scrimmage, tournament, playoff)

Sides
   id           (PK)
   TeamID       (FK to teams table)
   gameID       (FK to games table)
   score
   penalties
   powerplays
   notes

As shown, I would also leave out the "Outcome" field. That can be effectively and efficiently derived from the "Score" columns.