What happens if there are two ore more overtime periods (which could happen in the playoffs)?

[BurntSushi]

From curien on reddit:

I noticed that your schema the game table uses separate explicit rows for scoring each quarter. What happens if there are two ore more overtime periods (which could happen in the playoffs)? Come to think of it, I wonder how the GSIS data handles it (since, IIRC, every game has a "fifth" quarter score regardless of whether or not it goes to overtime).

If you had a separate table that stored (gsis_id, quarter, score) tuples, that wouldn't be an issue. Plus, it would more easily allow queries that treat quarters equally. E.g., it would be a lot easier to write a query that figured out which quarter had the most scoring. (I realize that scoring data is replicated in the play data, but I found that some extra points at least are missing, so it's not completely reliable.)

[BurntSushi]

I am hesitant to adopt an entity-attribute-value pattern in the schema, particularly when a reasonable solution is to just add another column for a second OT and hope that triple-OT never happens.

Actually, I don't think that's even possible because of the GSIS source data. Last year, double-OT actually happened in the DEN/BAL game. Here's the JSON. You'll note that only 5 quarters are reported, so that the first and second overtime periods aren't actually distinguished.

[BurntSushi]

Note though that quarters are distinguished in the play (and drive?) data, so that the game phase enumeration accounts for double overtimes.

[ochawkeye]

@BurntSushi

You'll note that only 5 quarters are reported, so that the first and second overtime periods aren't actually distinguished.

They actually are distinguished in the JSON data:

{"sp":0,"qtr":5,"down":0,"time":"","yrdln":"","ydstogo":0,"ydsnet":11,"posteam":"","desc":"END QUARTER 5","note":null,"players":{}}
{"sp":0,"qtr":6,"down":1,"time":"15:00","yrdln":"DEN 34","ydstogo":10,"ydsnet":13,"posteam":"BAL","desc":"(15:00) R.Rice up the middle to DEN 32 for 2 yards (D.Williams).","note":null,"players":...

notice "qtr":5 vs. "qtr":6. But I think the fact remains that there is precedent for such a situation and nothing broke.

[BurntSushi]

Yeah, that's what I meant by saying quarters are distinguished in play (and maybe drive) data.

This issue is about getting accurate quarter-by-quarter scoring for a game. The JSON data provides Q1-Q5 scores where Q5 apparently encompasses all OT periods. These correspond to the {home,away}_score_q[1-5] fields in the game table. This is in contrast to play data, where OT periods are distinguished.

Therefore, the only way to actually get correct scores accounting for more than one OT is to compute the scores from play data. Which can be inaccurate.

[BurntSushi]

Issue #6 should hopefully resolve this somewhat. Otherwise, we're at the mercy of the source JSON data.