@LoztInSpace said:
I disagree. For any team you can easily go back to the opponent's record via the GAME table and establish the opposing score, thus effectively giving each game 2 scores. From there, it is as easy or difficult as in your model with the added benefit of no corrupt data (e.g. a team marked as a win when it had a lower score)
Except that counting wins/losses is a big pain in the butt SQL-wise. If the score data is entered only once, and the win/loss field is generated in software upon every save, I'm fairly confident about keeping the data clean.
@LoztInSpace said:
You could have home venue on each team and another venue on where the game was actually played. That would allow you to model the scenarios you were talking about - i.e. either, none or both playing "at home".
For some sports (e.g. baseball), it's important which team is the home team -- and there can be a home team even if neither or both teams are playing at their home field. So for each game, you have to keep track of home team separately from venue and what each team's home field is.
@Pap said:
Is this a major professional sport that you are
following? If so, which? Surely, you're going to have many more reads
than updates, so I don't think a de-normalized games
table is out of
the question. But depending on what else will show up in the standings
besides W-L, you may want to pre-record that stuff also. So every time
a game is reported or updated, a function runs which updates a summary
table, which has primary keys team_id
and season_id
(for archival
purposes). This will keep your other tables completely normalized, but
the extra denormalized (summary) table thrown in as extra information
for speed. <snip>
Oh, that sounds like a great compromise! FWIW, I'm planning to use this season's MLB data for testing, and aiming for something usable for a wide range of professional and amateur leagues. And yes, there would be more reads than updates, as a game score should only be entered once (excluding operator error corrections and odd cases like rain delays), but can be displayed in many different contexts.
@RaspenJho said:
Would you not want to store your scores on a per-quarter/inning/round basis? We have office games whereas if your numbers = the last numbers
of the teams scores (you have two numbers), for any quarter, you win a
prize.
Interesting idea... I think it's out of scope at the moment, though.