Page 1 of 1

Where do I begin?(newbie trying to create a sports database)

Posted: Wed Jul 01, 2009 7:52 am
by sirflex10
Greetings everyone! I'm new to php and I'm a bit confused as to where to start. I'd appreciate any help that I can get. I'm trying to create a sports statistics database for an online sports video game (Madden football) that will track players stats such as passing yards, rushing yards, wins, losses, etc. The game automatically emails me a text file with the results of each game but I'm not sure where to start with the data that I have. I'm currently reading "database design for mere mortals" and I'm learning the importance of creating relevant tables and such, but after these tables are created in mysql what's the next step? Here's an example of the data I recieve in my email, how would I go about putting this data into mysql so that I can create an statistics sports database where the statistics can be sorted. (i.e. ascend or descend most rushing yards, etc.) Thanks in advance!!

Data example:

Here are the official results of your Madden NFL 09 game on 2009.6.30-20:52:52.

Player1 [Level 3] vs Player2 [Level 1]

Skill Points Won: 44
Skill Points Needed For Next Level: 738
My Rank: 10001
Opponent Rank: 10001


Mode: RANKED
Qtr Length: 5 minutes
Difficulty: All-Pro

Score breakdown:

1 2 3 4 OT F
Player1 0 10 6 0 0 16
Player2 7 0 0 3 0 10

Statistics Player1 Player2
Rushing attempts for 20 12
Rushing yards for 77 35
Passing attempts for 12 17
Passing yards for 118 121
Punts 0 0
Field goals for 1 1
Long field goals (40+) for 1 0
Rushing touchdowns for 2 0
Passing touchdowns for 0 1
Returns touchdowns for 0 0
Defensive touchdowns for 0 0
Turnovers for 1 0
Interceptions for 0 1
Sacks for 1 2
Number of third downs 6 7
Third down conversions 4 5
No-huddle plays 3 3
Audibles called 6 12
Hot routes used 7 4
Shotgun plays 4 6
Blitzes 12 20
Time of possession 9:46 10:13
2-point conversions tried 1 0
2-point conversions succeeded 0 0
Red zone field goals 0 1
Red zone touchdowns 2 1
Red zone opportunities 3 2
First downs 12 8
First down yards 71 31
Special teams yards 52 96
Punt return yards 0 0
Punt yards 0 0
Penalties 1 0
Penalty yards 10 0
First down rushing plays 10 6
First down passing plays 6 6
Second down rushing plays 7 4
Second down passing plays 5 7
Third down rushing plays 3 2
Third down passing plays 3 5
Fourth down rushing plays 0 0
Fourth down passing plays 0 0
Passes completed 10 10
Fumbles 0 0
Fumbles recovered 0 0
Possessions 5 5
Third downs, 3 or less yards 3 4
Successes 3 2
Third downs, 4 to 9 yards 0 1
Successes 0 1
Third downs, 10 or more yards 3 2
Successes 1 2
Hurries 3 4
Knockdowns 0 0
Passes deflected 5 1
Passes tipped 0 0
Offensive yards 195 156
Total yards 247 252
Return yards 52 96
Total touchdowns 2 1

Re: Where do I begin?(newbie trying to create a sports database)

Posted: Wed Jul 01, 2009 5:34 pm
by califdon
That won't be an easy task, if the data is received in that kind of a format. If you are stuck with that, about the only thing you can do is write a parser to read the file and identify data values and corresponding field names. If there's any chance that the format of the email might change over time, you have a real nightmare on your hands. Even if it doesn't, that's not an easy job for a beginning programmer!

You're right to start with defining your tables and fields, though. If your schema (tables and fields) doesn't follow relational database data normalization rules, forget about ever getting anything useful out of it. The initial schema design is the most important part of a database project of any complexity at all.