Database Design Ideas

[jason97673]

I am working on creating a website that has alot of quarterback statistics from the NFL. Mainly splits such as 3rd downs, playing from behind etc.

 

Right now I dont have a problem, but my idea of what to do is simply create a table for each split(such as 3rd downs, 1st quarter, etc). And in that table I have the Quarterbacks stats for the individual stat. But instead of making 100 tables for all the different type of split statistics, is there an easier way to do this.

 

My idea is to create a website that will have all the QB's on one page for a particular stat. But I will eventually want to also make an individual QB clickable and show his stats. Sort of how ESPN has it(http://sports.espn.go.com/nfl/players/splits?playerId=5536).

 

But if I create many different tables, I dont think it is possible to list that QBs splits all on one page using PHP.

 

Having one table with 1000 fields doesnt appear to be good database design either.

 

Basically how would a site like ESPN probably design there player statistics database or any of the other sports websites?

 

Basically I am just looking for ideas as to what the best database design would be.

 

Thanks for any ideas.

[keeB]

How in depth do you want to be? Will every play potentially have a stat? Only some plays?

 

For something completely in depth that would take forever................ Break down could be like the following?

 

Division

Team

Game

Player

Play

 

From there, you can gather as many statistics as you want

[jason97673]

How in depth do you want to be? Will every play potentially have a stat? Only some plays?

 

For something completely in depth that would take forever................ Break down could be like the following?

 

Division

Team

Game

Player

Play

 

From there, you can gather as many statistics as you want

 

Well Im confused as to what you mean exactly. Basically, I will not be doing the stats myself. Pretty much I am just gathering the stats from different resources and compiling them all in one place. My main reason for doing this is how I like splits statistics and most sites only have year by year and totals through out the career. And no sites from what I have seen have side by side comparisons. Pretty much I will just be including  stats such as the different quarters, down, behind, ahead tied, in a win or loss etc.

[jason97673]

Does anyone else have any ideas to this?

[able]

No idea about usa football, you'd need to explain it without the reliance on a common terminology.

[jason97673]

Well if you take a look at any sports website, just by looking at it, I think someone should beable to give me an idea of how to build a database like sites such as espn, nfl, etc.

 

I dont think you really need to know about the NFL to understand what Im attempting but that is just what the site is about.

 

Simply if you look at http://sports.espn.go.com/nfl/players/splits?playerId=5536 it would tell me that every player in the nfl has an id. But then there is like 50 different types of statistics for each player(Different statistics being the column on the farthest to the left on that link - totals, home, away, etc).

 

For each statistic there would be many different fields(This would be the column headers on the link I showed - cmp, att, etc). If I want about 25 different statistic types and each type has 10 fields to it, it would be a little over 250 fields in one table and that seems like bad database design.

 

Perhaps, I can do what I am already doing which is having a different table for each type of statistic and those tables have their fields. Which leads to my problem of combining all the tables of data onto one page in PHP so you can make a player clickable and see all of his statistics if you do not want side by side rankings which is what I have right now.

 

Hopefully that makes it more general.

[Cardale]

Sounds like a great idea, but its pretty hard to completely design your database without knowing every part of your idea.  Since your the only one that can know exactly what you want..I suggest trying different methods and finding out what you like best.

 

From what I understand you want to compare individual games of quarterbacks and how they performed with win / lose comparison.

 

I think once you decide on a good way to store this data which to me would be easy, but requires more PHP then database design to do it.

 

Here is what i would do - I would start by adding all the possible player information I want to store.  Then stats I want to record and place that in a table called states.

 

I would probably also add a game schedule table...this is exactly needed depending on how you want to do it, but for me I would rather have this information to pull from rather then entering it individually.

 

players - table - id - name of player - posistion - overall stats - any other comparison fields you will add up using your statistics programming can be stored here.

 

stats - table - player id - stats you want to record - game date which if you used the game schedule table idea then all you would do is pull a id or number from that table

 

then put it all together via PHP

 

Now this is a short summed up version I didn't put any planning into this just what I would do thinking from the top of my head.  Hope it helps some what.

[spikeon]

how about:

 

mysql:

 

table name: QB

row name: Name type=VARCHAR 50 KEY

row Info: type=TEXT

 

name example: my name

info example: Birthday = 6/3/1978 | foo = bar | poo = Pants

 

name example: my name1

info example: Birthday = 6/3/1978 | foo = bars | poo = Pants

 

 

php

 

to get all of the stats on one player:

$q = "SELECT * FROM QB WHERE Name = "my name" LIMIT 1";
$r = mysql_query($q);
while($player = mysql_fetch_array($r)){
echo "<h1>".$player[name]."</h1>";

$infos = explode("|", $player[info]);

echo "<table>";
foreach($infos as $info){
list($descriptor, $description) = explode("=", $info);
echo "<tr><td>".$descriptor."</td><td>".$description."</td></tr>";
}
echo "</table>";
} 

 

that'd echo this:

 

<h1> my name </h1>
<table>
<tr><td>Birthday</td><td>6/3/1978</td></tr>
<tr><td>foo</td><td>bar</td></tr>
<tr><td>poo</td><td>pants</td></tr>
</table>

 

to get a single stat from all players:

 

$stat = $_GET[stat]; (stat will be foo)
$q = "SELECT * FROM QB";
$r = mysql_query($q);
echo "<table>";

while($player = mysql_fetch_array($r)){
$stuff = "<tr><td>".$player[name]."</td>";

$infos = explode("|", $player[info]);

foreach($infos as $info){
list($descriptor, $description) = explode("=", $info);
if($descriptor == $stat){
$stuff =  "<td>".$description."</td></tr>";
echo $stuff;
}
}
} 
echo "</table>";

 

that'd echo:

<table>
<tr><td>my name</td><td>bar</td></tr>
<tr><td>my name1</td><td>bars</td></tr>
</table>

[jason97673]

Thanks. I think that helps an aweful lot. Ill see what I can do wth it.

[spikeon]

no problem

 

there may be syntax errors, but they'll be easy to find