Design: Multiple Values in One Column or Many Columns?

Question

I am designing a database based on NBA stats.

For a particular part, I have:

Game: [ Id, HomeTeam, AwayTeam, Date, GameTime … ]

GameScore: [ Id, GameId(FK), QuarterOne, QuarterTwo … ]

For game score, there is quarter one to quarter four. How should I design it it to include overtime quarters. What is best design method for this situation?

Should I put the values into one column?

Or should I have a separate table as follows:

Game: [ Id, HomeTeam, AwayTeam, Date, GameTime … ]

GameScore: [ Id, GameId(FK), QuarterOne, QuarterTwo … ]

OvertimeScore: [ GameScoreId(FK), Quarter, score ]

The method above, will include the overtime score in the table "OvertimeScore", where quarter column stores the quarter value, and score column stores the score value.

It doesn't seem right design for some reason. Please help thanks.

Answer 1

Do not have GameScore or OvertimeScore. Instead have QuarterScore keyed by GameID and Quarter. For normal games there will be four rows per game. For overtime there will be 5, 6, or however many are needed. Total score and a count of quarters can be summarised as a column in Game, if this is useful.

Answer 2

If you created an OverTimeScore table, you'd then need to add an INT or BIT field to GameScore or Game table to check if overtime exists or not. Are you also going to store the total score? Or just each quarter score and calculate the total in your app/query? If this were me, I would add a total score field and an overtime field into my GameScore table, I don't see a reason to break it out into its own table. Populate the field if there is overtime, otherwise leave it NULL. You can then just work your queries to check is null. 101 ways...