sport statistics in a database

[Rohan72]

For a website of a sportsteam, i would like to set up a database with the individual statistics of each player.
Because I'm not that familiar with more complex databases, I'd like to get some help/information on how to set this one up

I'm wondering if i need to make more then one table or if i could put all the statistics together in one table.

For each player I have 4 different stats. and the number of games (and opponents) has yet to be determined because of the cup games that run throughout the season.

the way i was thinking is to set up 4 different tables (one for each stat) and then combining them when i get the data in php (but how to do that is another question for when i've set the table(s) up).

Or is there maybe another way to create this kind of table?

[boogyman]

I think that would be too much normalization.

the best way to determine what you should (not) create a separate table is if 1 value is going to be repeated extensively.

for instance you have 10 teams that are playing 5 different types of games head to head. in this instance I would create 1 for the teams and 1 for the games and a reference table to list the results of each game.

TABLE teams (
     tid
     tname
     tstadium
etc...
);

TABLE game (
     gid
     gname
     grules
etc...
);

TABLE match (
    gid
    tid1
    tid2
    winner
    score
etc...
);

this way you can separate the different matchs being played, which teams they were played against, and some other information about it.
however it wouldn't be beneficial do create another table for the records... that can be stored in just the team table... otherwise you would need to create 1 table for each stat and 1 for a reference, so you see.. its getting to normalized.

TABLE teams (
     tid
     tname
     tstadium
     twins
     tlosses
);

[Rohan72]

The database wouldn't be needed for the scores of the games, just the individual player stats.
So I guess for me it would look something like this.

TABLE player (
     pid
     pfname
     pname
);

TABLE opponent (
     oid
     oname
     odate
     olocation
);

TABLE stats (
     sid
     pid
     oid
     spoints
     sassists
     srebounds
);

and the data in the tables should look like this

Table player:
1, Michael, Jordan
2, Scottie, Pippen
3, Dennis, Rodman


Table spponent
1, Detroit, 20/11/96, home
2, Cleveland, 23/11/96, away


Table stats
1, 1, 1, 40, 7, 4
2, 2, 1, 26, 5, 6
3, 3, 1, 9, 3, 14
4, 1, 2, 49, 3, 6
5, 2, 2, 17, 7, 2
6, 3, 2, 11, 1, 12

Or is there another way to store this kind of data easily?

I think the stats table would get very big this way? maybe store the individual stats each in a seperate table?

[boogyman]

well the data is going to need to be there anyway correct? so what does it matter if its in a new table?
I think the set-up you have there is the most normalized you should have it, except I would also track the team the person played for. People get traded between teams alot, so rename the sopponents to teams table and in the stats table create a field for the team the player plays for.... so it would be

TABLE stats (
     pid (player)
     tid (their team)
     oid (who they played)
     where (home / away)
(individual stats eg   pts/rebounds/assists/blocks/fouls etc...)
);

now i think it would be best again to keep the home / away inside the stats table and not create a new table because you can make that an ENUM type field with only those choices, and I that is something that is static.
While you do not need to track team stats at the moment there might be a time that you would like to track those, and believe me its alot easier designing for it from the start, then to need to do it later and have to re-create the entire database. to incorporate this one piece of information

[Rohan72]

There is no need for team stats because I already have a great script for that
(it's called LMO).
The stats I need is just for our team. Each player has it's own page, and we'd like to present some basic stats for them on that page. At the moment, it's just all plain html. a database would make it much easier to update.
Here is an example of how it is at the moment :
http://www.sportingneerpelt.be/h1_1_15.html

[boogyman]

okay thats fine. If possible I would suggest that you modify the LMO script to include abiility to track players, however if you cannot do such a thing, then the layout you had for the database should do just fine.

But I do want to remind you that the information is going to need to be there no matter what so it really would not be beneficial to create separate table for each stats. Normalizing a database is something that should occur when you have a value that is repeated over and over and over... what you are talking about is stats that will be different for each player per team... thus you would really just introduce another layer of complexity that serves no benefit, and would most likely hinder the query and bog it down with unnecessary joins.

[Rohan72]

Ok. Thanks...

I really wanted to get a good start to get the right tables to start with.
Now I can set them up correctly and put in all the data I already have.

Thanks to some other earlier php problems and questions, I now got a basic understanding how to recall the data. So then it's time for me to start experimenting on it. And I would have hated it to get the script right only to find out my table setup was useless.

[boogyman]

oops one thing i just noticed is that in your spponents table you have a date and a place? dont use that... just put the team information there, and in your stats table put if it was home / away and the date. otherwise you are really creating another stats table for no reason because its very rare that any team has 2 games in the same day, in which case you would want to track the times not the date. so keep the teams (opponents) table just information about the actual team (id, name, stadium)