Table design question for recording game schedule and stats

G

Guest

Hello,

I would like to create a scheduling database for football games which also
would contain the data on selected stats. Two design questions:

1. Is it best to have the schedule data separate from the stats?

i.e. one possible design scenario:

Schedule Table
gameId | Hteam | ATeam | DateOfGame |


Stats/Score Table
gameId | HScore | AScore | HPassingYds | APassingYds | HrushingYds |
ARushingYds

Another design scenario would be:

gameId | Team | HomeAway | Date | Score | Passing Yds | RushingYds
1 A away 9/10 23 345
89
1 B home 9/10 27 256
202

I was leaning towards this option so it would make for easier loading of
data but the queries may be a bit more complex.

any thoughts?
 
J

Jamie Collins

I would like to create a scheduling database for football games which also
would contain the data on selected stats. Two design questions:

1. Is it best to have the schedule data separate from the stats?

i.e. one possible design scenario:

Schedule Table
gameId | Hteam | ATeam | DateOfGame |

Stats/Score Table
gameId | HScore | AScore | HPassingYds | APassingYds | HrushingYds |
ARushingYds

Another design scenario would be:

gameId | Team | HomeAway | Date | Score | Passing Yds | RushingYds
1 A away 9/10 23 345
89
1 B home 9/10 27 256
202

I was leaning towards this option so it would make for easier loading of
data but the queries may be a bit more complex.

any thoughts?
Click to expand...

Your scenario is a pretty good match for the following <g>:

ON POFN* AND POOD* - TWO COMPLEMENTARY DATABASE DESIGN PRINCIPLES
* POFN: Principle of Full Normalization; POOD: Principle of Orthogonal
Design

Hugh Darwen: "consider the soccer team's fixtures for the 2005-6
season. The following is in 5NF: FIXTURE{Opponents, Venue, Date,
GoalsFor, GoalsAgainst}, keyed on Date, and also on {Opponents, Venue}
(where Venue is Home or Away).

"But this FIXTURE suffers from an update anomaly arising from lack of
independence. The dates, venues and opponents for all fixtures are
determined before the season starts, and it is useful to record them
independently of the results, before the games in question are
played."

Jamie.

--
 
G

Guest

not sure if that is an answer or not. <g>

from what i gather the schedule should be in one table with another table to
record the stats?

tblSchedule
---------------
ScheduleId (PK)
GameDate
HTeamCd
ATeamCd

tblResults
--------------
ScheduleId (PK)
TeamCd (PK)
Score
PassingYds
RushhingYds
....
 
G

Guest

Depending on your level of detail, you might want to have a game yardage table:
ScheduleID
PlayerID
YardageType (rushing, passing, puntreturn, KOReturn, Reception, ..)
Yards
PointsScored

This table would give you the abilty to compute individual stats, as well as
the game stats.
 
Top