Database design for junior football two divisions east/west with four teams in each division -

current design:

• gamestable: gameid, dmy, starttime, dayplayed, weeknumber, hometeamdivisionid, hometeamid, awayteamdivisionid, awayteamid, hometeampointsscored, awayteampointsscored, win-loss

• hometeamdivisiontable: hometeamdivisionid

• hometeamtable: hometeamid

• awayteamdivisiontable: awayteamdivisionid

• awayteamtable: awayteamid

do need more tables, or different tables?

initial observations

you not need separate tables 'home team' , 'away team'. think it: have teams; these organized divisions; play games. in given game, 1 team home team , 1 team away team, different games, given team can home team or away team. given team belongs 1 division @ time. need design this:

• division: division id, name (...)

• team: team id, division id, name (...)

• game: game id, date played, start time, home team id, away team id, home team score, away team score (...)

clearly, there constraints on values in game table, such 'home team id' not equal 'away team id'. there might more complex constraints too: 2 teams must in same division (if there no inter-divisional play). have 2 foreign key constraints game team.

from these, else can calculated. might need 'calendar' table define 'week number'; easiest way determine week of season given game played in:

• calendar: date, season id, week number (...)

if have deal 'team a' in west division in spring 2012 in east division autumn 2012, etc, need more complexity, while have record 1 season @ time, team in same division, you're pretty go.

---

now more information available

the overall goal record games played in league on number of seasons. teams making league stable, , there 2 divisions, teams can move between divisions in different seasons (but not during season).

the first necessary change team table; division id property of team particular season, not permanent attribute. calendrical issues more important too.

• season: season id (pk), name, start date, end date (...)

• division: division id (pk), name (...)

• team: team id (pk), name (...) (no division id here)

• seasondivisionteam: season id, division id, team id (pk 3 attributes)

• game: game id (pk), date played, start time, home team id, away team id, home team score, away team score, venue id, (...)

• calendar: date (pk), season id, week number (...)

the name seasondivisionteam not good; i'm drawing blank on better name, though. defines teams in each division each season. 'all key' table, 3 separate foreign keys. there constraints between season table (with start , end dates) , calendar table. dates designated specific season in calendar table must not fall outside range of dates designated same season in season table. alternatively, drop season dates season table , let calendar table define valid ranges of dates.

you'd end statistical tables summarize data in main operational tables. example, calculating standings in division particular date doable, modestly expensive computationally. might have summary table recording 'end of season' standings. have entries in end of each week if wanted to.

i'm not sure what's best playoffs. game table able record raw data. might want add venue column record game played. since junior division, can imagine have knockout tournament on 1 or 2 days @ end of season — or maybe tiered tournament round-robin phase , knockout phase. in case, might have cluster of games played in short time. or might have games spaced out more regularly. structure of games table can record of these variations. however, might find sensible devise table defining structure of playoffs. works best may depend on how stable playoff organization has been. that, more information needed. however, simple matter of recording games, outlined games table has no problem. issue how humans attach meaning playoff games.