Hello all,

I'm relatively new to database design and I'm looking for a nudge in the right direction here.

I'm designing a web page for a fantasy baseball league that is all friends and family. The league will span several years, fortunately we've only logged one full season in, so I can get the design right at the beginning.

At first I had a table for each year with the team_id as primary key. I realized though that might not be the best way of doing things, especially when I have to calculate the total for an entire category. Example, Home runs for 2007, home runs from 2008, etc... for a specific team. Doing it this way, I would have to access several different tables to add up the totals.

My next thought would be to have a table for each team (8 teams) and use the year as the primary key. Several spots throughout the web site will require totals for each field to be added. Also, several spots will require a function that can sift through each table (and year) and determine the highest number. So for example, the season record for home runs, it would have to look through each table and year and determine the record.

I'm kinda just wondering if I'm on the right track here or if I'm looking at it completely wrong. I also would like to know if keeping the site dynamic like this will slow it down very much? I appreciate any help or advice you can give.

Thank you
bl

I would use only one table for each year and for all teams, keyed by Year and Team (TeamId), and joining to another table Team with TeamId/TeamName

e.g.
Year, TeamId, Other fields ...
2007 A
2007 B
2007 C
2007 D
2007 E
2007 F
2007 G
2007 H
2008 A
2008 B
2008 C
...

TeamId, Team Name
A Team A
B Team B
C Team C
D Team D
...
H Team H

I disagree that you would need a table for each year. You could just have one field named 'year'. And, I think you can put everything in one table if this is for a small project. But, it's hard to give you the best advice on how to structure your database without knowing what information you're trying to store and how the information is obtained (ie: is the info already available or does it have to be calculated based on another field?). Can you provide more details?

Thanks for the reply. The information is already available. None of the fields will have to be calculated from other fields.

There are 8 teams and each team will require a row of information in the db for a particular year.

For each year the information that will be stored include:

games_won | games_lost | runs | home_runs | rbi | avg | strikeouts | wins | saves | era | whip | titles_won | playoff_app | trophies | etc...

There's more, but it's all basically the same. Eventually calculations will have to be made, but I don't think they would have to be stored in a db. Well actually I'm not sure if that is the best way or not. For example, if I want to add all time runs scored for a particular team, my idea was that I would just add the years up for that team and display them on the web page. Or would it make more sense to store that info in a db as well?

The web page will display things such as records (yearly,all time), franchise wins, losses, championships won, and related information such as that.

I hope this helps explain what I'm trying to do, thanks again for the help.