Table design question for recording game schedule and stats

[JP]

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?

 

[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?

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.

--

 

[JP]

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
....

 

[Jamie Collins]

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

It appears that way to me.

Jamie.

--