Database question

[BigToque]

I'm trying to create a stats database for my hockey team and I've got a noob question...

I want to be able to be able to pick players and show their career stats over multiple seasons. I know that I can create a table that holds all the players and their stats for the season. Does this mean that for every season I create a new table that contains the players and their stats, then for a running total I need another table that selects the player data from each of the seasons tables that the player played in.

Is this right?

 

[ggavinmoss]

You can create one table with a "season" column... allowing you to do yearly or lifetime stats.

-geoff

 

[notfred]

I know nothing of hockey, so I'll make a demo table here with baseball as an example:

Table fields:
player_name, year, earned_run_average, batting_average, stolen_bases, home_runs, etc...

Enter all your stats in that table. The same player can have multiple entries, and they'll all be distinguishable by the "year" field.

The running total should be a script that adds up all the stats fora particular player.

 

[blackdogdeek]

you should be able to create another column that has the number of season or like the year of the season(or any other distinguishing characteristic of the season).

this way every player will have multiple rows, 1 per season.

to get the totals, you should be able to sum or avg the contents of each stat's columns over the course of the entire career or over selectable seasons.

 

[austin316]

Originally posted by: Stefan
I'm trying to create a stats database for my hockey team and I've got a noob question...

I want to be able to be able to pick players and show their career stats over multiple seasons. I know that I can create a table that holds all the players and their stats for the season. Does this mean that for every season I create a new table that contains the players and their stats, then for a running total I need another table that selects the player data from each of the seasons tables that the player played in.

Is this right?

Create a table for the players, then link to another table for the stats with year and playername/id as the primary key.


I'm pretty sure this would work.

 

[brtspears2]

I personally would go

Players(pid, first name, last name, team, age, birthday, height, weight...)
Stats(pid, games played, minutes played, goals, penality, penality minutes, ...)
With Inner join Players.Pid ON Stats.pid.

This way when you update one record, like a player fibs on their weigh, moves, changes teams, ..., you don't have to update a bunch of records, just one.

 

[IHateMyJob2004]

http://www.expressrollerhockey.com

Go there and i you like it, contact the web master.

PS: that's my roller hockey team. I'v ebeen getting worse every season

 

[austin316]

Originally posted by: brtspears2
I personally would go

Players(pid, first name, last name, team, age, birthday, height, weight...)
Stats(pid, games played, minutes played, goals, penality, penality minutes, ...)
With Inner join Players.Pid ON Stats.pid.

This way when you update one record, like a player fibs on their weigh, moves, changes teams, ..., you don't have to update a bunch of records, just one.

what he said is what I was trying to say.

 

[IHateMyJob2004]

Originally posted by: IHateMyJob2004
http://www.expressrollerhockey.com

Go there and i you like it, contact the web master.

PS: that's my roller hockey team. I'v ebeen getting worse every season

To follow up, I think we keep stats for every single game. Every goal, penalyty, etc is a stat. I'd imagine hte DB has season ID in it along with game number for each season, etc.

Basically one big table.

For season stats, the table is querried to get the appropriate result.

 

[BigToque]

Originally posted by: brtspears2
I personally would go

Players(pid, first name, last name, team, age, birthday, height, weight...)
Stats(pid, games played, minutes played, goals, penality, penality minutes, ...)
With Inner join Players.Pid ON Stats.pid.

This way when you update one record, like a player fibs on their weigh, moves, changes teams, ..., you don't have to update a bunch of records, just one.

With something like this, will I create a new stats table for every season? That way I can access stats from previous seasons, but also create totals from all seasons.

 

[EagleKeeper]

Originally posted by: Stefan

Originally posted by: brtspears2
I personally would go

Players(pid, first name, last name, team, age, birthday, height, weight...)
Stats(pid, games played, minutes played, goals, penality, penality minutes, ...)
With Inner join Players.Pid ON Stats.pid.

This way when you update one record, like a player fibs on their weigh, moves, changes teams, ..., you don't have to update a bunch of records, just one.

With something like this, will I create a new stats table for every season? That way I can access stats from previous seasons, but also create totals from all seasons.

Just expand the table. Have a field that indicates which season so you can filter if desired

 

[Silverbullet28]

Originally posted by: Stefan

Originally posted by: brtspears2
I personally would go

Players(pid, first name, last name, team, age, birthday, height, weight...)
Stats(pid, games played, minutes played, goals, penality, penality minutes, ...)
With Inner join Players.Pid ON Stats.pid.

This way when you update one record, like a player fibs on their weigh, moves, changes teams, ..., you don't have to update a bunch of records, just one.

With something like this, will I create a new stats table for every season? That way I can access stats from previous seasons, but also create totals from all seasons.

No, I think you would just keep adding new players and their information to the existing tables. You could get the information from each individual season by making a query involving the "Year" field, as long as you have a year field in the Stats table.

 

[Mr.IncrediblyBored]

Originally posted by: austin316

Originally posted by: brtspears2
I personally would go

Players(pid, first name, last name, team, age, birthday, height, weight...)
Stats(pid, games played, minutes played, goals, penality, penality minutes, ...)
With Inner join Players.Pid ON Stats.pid.

This way when you update one record, like a player fibs on their weigh, moves, changes teams, ..., you don't have to update a bunch of records, just one.

what he said is what I was trying to say.

If the database is being used for long term data mining, this design is quite flawed. What if Player A has a 7 year career. 4 of those years are in Team X and 3 years with Team Y. Using your design you store only the player's current information. How would you query the system for Player A's statistics for only his first team? You lose the connection between the player's attributes and his past statistics.

It may seem redundant but things like weight, age, and team are all moving targets and can change each year.

 

[BigToque]

Originally posted by: Silverbullet28

Originally posted by: Stefan

Originally posted by: brtspears2
I personally would go

Players(pid, first name, last name, team, age, birthday, height, weight...)
Stats(pid, games played, minutes played, goals, penality, penality minutes, ...)
With Inner join Players.Pid ON Stats.pid.

This way when you update one record, like a player fibs on their weigh, moves, changes teams, ..., you don't have to update a bunch of records, just one.

With something like this, will I create a new stats table for every season? That way I can access stats from previous seasons, but also create totals from all seasons.

No, I think you would just keep adding new players and their information to the existing tables. You could get the information from each individual season by making a query involving the "Year" field, as long as you have a year field in the Stats table.

so if you look at the stats table records I would have something like this:

player1, year1, goals, points, etc
player2, year1, goals, points, etc
player1, year2, goals, points, etc
player2, year2, goals, points, etc

so there is a new record for every player each season in the stats table?

 

[Mr.IncrediblyBored]

Originally posted by: Stefan
so if you look at the stats table records I would have something like this:

player1, year1, goals, points, etc
player2, year1, goals, points, etc
player1, year2, goals, points, etc
player2, year2, goals, points, etc

so there is a new record for every player each season in the stats table?

Exactly.

 

[notfred]

You should really pick up a book on SQL database design, it's not real practical to have us teach you the whole thing in this thread.

And your last psot says exactly what I said yesterday in the second reply to this thread.

 

[BigToque]

Originally posted by: KnightBreed

Originally posted by: Stefan
so if you look at the stats table records I would have something like this:

player1, year1, goals, points, etc
player2, year1, goals, points, etc
player1, year2, goals, points, etc
player2, year2, goals, points, etc

so there is a new record for every player each season in the stats table?

Exactly.

alright... cool

thanks for all the help guys

 

[BigToque]

Originally posted by: notfred
You should really pick up a book on SQL database design, it's not real practical to have us teach you the whole thing in this thread.

And your last psot says exactly what I said yesterday in the second reply to this thread.

true...

if you can believe it, I actually I missed your first post in the thread when I looked at the thread again this morning

 

[mugs]

All I have to add to the advice in this thread is that I'd break it down further by game, so you enter the stats after each game and it keeps a running total for the current season. You could do the same thing with update queries if you only have one record per season, but I prefer to store the "source" values rather than calculated values (totals).

I'd also post this in the Programming forum.

 

[Silverbullet28]

Originally posted by: Stefan

Originally posted by: Silverbullet28

Originally posted by: Stefan

Originally posted by: brtspears2
I personally would go

Players(pid, first name, last name, team, age, birthday, height, weight...)
Stats(pid, games played, minutes played, goals, penality, penality minutes, ...)
With Inner join Players.Pid ON Stats.pid.

This way when you update one record, like a player fibs on their weigh, moves, changes teams, ..., you don't have to update a bunch of records, just one.

With something like this, will I create a new stats table for every season? That way I can access stats from previous seasons, but also create totals from all seasons.

No, I think you would just keep adding new players and their information to the existing tables. You could get the information from each individual season by making a query involving the "Year" field, as long as you have a year field in the Stats table.

so if you look at the stats table records I would have something like this:

player1, year1, goals, points, etc
player2, year1, goals, points, etc
player1, year2, goals, points, etc
player2, year2, goals, points, etc

so there is a new record for every player each season in the stats table?

That would work. But you might add a field that contains a unique number for each player, because otherwise you might have duplicate data in your records and it could cause problems when making queries down the road.

unique #1, player1, year1, goals, points, etc
unique #2, player2, year1, goals, points, etc
unique #1, player1, year2, goals, points, etc
unique #2, player2, year2, goals, points, etc