One database or Many?

[tazboy]

I'm making a program to keep stats for football. Would it be best to make one database for each game or should I keep the entire season in one database? To go one further, should I make it so every season is on one database or should I split each season up?

I'm not sure which is best, especially since I will be calculating averages for each player over one year. That would make it so I would have to connect to over ten databases just to find an average over one season. Then if I want to do that over four years, well you get the picture.

Seems like it would be best just to do it in one database but I'm not sure.

Thanks for any suggestions.

[ASWC]

Best is always to keep it in one database for each project. You then break everything down to tables.

[wvxvw]

One database but different tables... IMO. But it really depends on what will be faster (how many entries would be per average table / how often would you need to access different tables during single request).

[tazboy]

Thanks for the replies. Looks like I'm going to go with one database having multiple tables. That way I don't have to work with multiple connections.

Thanks again

[JeTSpice]

I agree - one database. if you can, try to make all the calls in one file. we have an app that was made by another company, and all the db calls are spread out through 20,000+ lines. We are in the process of bringing it in-house, and we don't have the resources to find all those db calls. We are forced to naming our staging site and connections after the other company's naming convention (which includes their company name.)

So, even if you're dealing with many tables, attempt to keep your db calls in one file for future ease of maintenence.

[mattb]

Sorry this is a late reply... your DB should be defined in a way that should NOT require you to create a new table for each game (I can't tell if that's what's being suggested here or not).

As a simple example, you'll have a table that describes the seasons, a table that describes the games and a table the describes the players, for example:

SeasonsTable - Id, Year
TeamsTable - Id, TeamName
GameTable - Id, SeasonId, TeamAId, TeamBId, GameDate
GamePlayers - Id, GameId, TeamId, PlayerName, etc...

HTH