Hey all, I'm working on a sports database and am curious how you would do this. This particular situation is for collegiate football (american) and basketball.
Individual games are grouped together in a week. And weeks are grouped into seasons. I need to be able to pull a team's games for the entire season, as well as pull all the games played in a particular week.
Weeks typically run Monday through Sunday with the exception of the final week (bowl games) which span several weeks. Seasons are typical August through January. I will insert games into the database with their date, teams, location, etc.
My question is how would I go about setting this up. I'm thinking I need a seasons and weeks table each with a startDate and endDate field. I'm pretty sure that would be the right way of doing things.
If so, then is there a simple way to select all games from a week or season? I'm thinking it will require several queries to do.
To select all games in a week:
SELECT startDate and endDate FROM weeks WHERE (weekID = 1);
then throw that into
SELECT * FROM games WHERE (game.gameDate BETWEEN $startDate AND $endDate);
Similar queries to select a team's season schedule:
SELECT startDate and endDate from seasons WHERE (seasonID = 1);
then using those results
SELECT * FROM games WHERE (game.gameDate BETWEEN $startDate AND $endDate) AND (game.team = 1);