database design

[bschultz]

I need some advice on database design and layout.  I’m going to begin working on a project soon for a sports league.  I want to be able to enter and display game results and statistics for a variety of sports.  I would like to be able to layout the code in such a way that it works for EVERY sport.

 

Here’s my problem.  Each sport has a different number of statistics that are kept.  Plus (soccer, baseball and hockey etc.) have stats specific to certain positions, where other sports (basketball volleyball etc.) use the same stats for all players.

 

I’m thinking that I’ll need two tables…one for game results (score, opponent and such) and another for the individual player stats collected.  I also haven’t figured out if I want to keep the players names and such in a separate table or in the “stats” table.

 

Since I’d like to keep the database the same structure for all sports, would a config file for each sport be in order?  That way I could tell the loops and such in the php code which table columns need to be manipulated based on what the sport is.

 

For example:

In baseball-

(column 1 – games played) (column 2 – at bats) (column 3 – hits) (column 4 home runs)

 

Then to determine the players batting average, I need to tell it to divide column 3 by column 2

 

I am thinking that I’d need that info in the config file so that the code would work on multiple sports.

 

So, two questions.  If you were designing something like this, what structure would you use, and would you use sport-specific config files for the math work…or would you go another route?

 

Thanks.

 

Brian

 

[fenway]

I think it would be too complicated to try and squeeze all of these sports together -- group whatever generic fields you can (e.g. player names), but otherwise, have separate tables for each sport.