Table Design Question -- Baseball Related

[roberemok]

Hello,

I'm not sure why I'm drawing a blank but I am. Could I please get a few suggestions on how you might organize these fields into tables?

These are not baseball stats fields. I'm keeping track of how often teams win over the years. I would like to do generate a team webpages showing all historical wins, losses and championships.

TeamID
TeamName
Year
Wins
Losses
AL_DivisionChampion (3 divisions in each league EAST,WEST, CENTRAL)
NL_DivisionChampion (3 divisions in each league EAST,WEST, CENTRAL)
AL_WildcardChampion
NL_WildcardChampion
ALCS_Champion
NLCS_Champion
WorldSeriesChampion

Initially I thought about YES/NO fields but there are so many variables. I need to know STL won the division but also that it was the NL Central.

Any help would be greatly appreciated.

Thanks
Rob

 

[roberemok]

So far

I have started with:

tblLeagues
LeagueId - P
League

tblDivisions
DivisionID
Division
LeagueID - F

tblTeams
TeamID - P
TeamName
DivisionID - F

tblSeasons
SeasonID - P
Year
Wins
Losses

tblTeamsJunction
TeamID - P/F
SeasonID - P/F

Now my dilemma is where to store the championship info and when they won? Divisions, Leagues, WS, etc.

 

[Pat Hartman]

The first three tables are fine. Here's a suggestion for the others. This structure allows you to have as many championships as you like. I know these things don't change often but occassionally they do. This structure accomodates that.

tblSeason
SeasonID - P
SeasonYear

tblChampionships
ChampionshipID - P
ChampionshipName

tblSeasonWinners
SeasonID P/F
ChampionshipID P/F
WinningTeamID - F
LoosingTeamID - F

 

[roberemok]

Thanks for your help. I'm still not sure what I have done is right.

ie How do I handle the fact each league has EAST,CENTRAL and WEST Division. Would I enter each of these name twice and make sure I use the right league. NL - EAST as well as AL East. It seemed wrong to enter EAST and EAST in the Divisions Table. What do you think? At first this seems like a one to many (leagues to divisions) but then I wonder can one division ie EAST belong to both Leagues?


I also want to keep teams Wins and Losses but not sure where they would get added.

I like your championship idea. I'm assuming I would just list them all. ie ALCS, NLCS, World Series, ALDS, NLDS, etc. Where would add if someone wins a division? ie AL EAST The same table?

Thanks again
Rob

 

[mresann]

I have a sports betting database I'm trying to gin up interest in Vegas, so I not only have to deal with baseball, but virtually every other sport that can be wagered on. As such, I had to devise the best database structure for each league, division, or conference; not just for baseball but for football, basketball, hockey, Aussie Rules football, Superleague rugby, etc. My three-tier system works well enough for me. For just baseball, though you probably just need two tiers.

Technically, if you were to COMPLETELY normalize your database, you would include a join table combining the League (National or American) with Division (East, Central, West). However, this may be overdoing it for your purpose. I would just go with the League (National, American), then Divisions (NL East, NL Central, and NL West, all foreign keyed to the National League; with AL East, AL Central, and AL West, all foreign keyed to the American League). Then associate each Team with a division.

 

[roberemok]

mresann,

Thanks for your suggestions an ideas. Maybe it would just be easier to do it your way.

Any ideas on where to store teams wins and losses? Maybe in the Season's Table.

Still unsure where Division Championships would go. I guess I could just store AL East Pennant and NL East Pennant with AL Pennant and World Series.

Thanks
Rob

 

[mresann]

Since the teams are already associated with a division and, through the division db, a league, you don't have to replicate those attributes. Unfortunately, though, I can't really give you anything I have because I have serveral more factors to consider (players can play for more than one team, etc), umpires, stadiums, etc.

Generally, I'd go:

LeagueID
League (American, National)

DivisionID
LeagueID (FK to League)
DivisionName (AL East, AL Central, AL West, NL East, NL Central, NL West)

TeamID
DivisionID (FK to Division)
Team Name (Boston, New York, etc)
Team NickName (Red Sox, Yankees, etc)

EventTypeID
EventType (Regular, Wildcard, Divisional, World Series)

EventID
AwayID (FK to TeamID on Team)
HomeID (FK to TeamID on Team)
EventTypeID (FK to EventType)
EventDateStart (Start datetime of game; doubleheaders have same date)
EventDateEnd (End datetime of game; query can calculate time of game)
AwayScore
HomeScore
Time (For baseball, it is simply the innings played, dividable by 6 outs)

Note you don't have wins and losses; that should be calculated through query. In addtion, for post season play, "Game 3 of ALCS" can be figured out through query as well.

I may be missing a few things, but this is the gist of the normalization for a good baseball database.

ONE MORE THING: In my database, I don't have "Seasons"; rather I have a hard date that I use to divide seasons. For instance, I use January 1 for baseball, May 1 for Football, and July 1 for Basketball and Hockey. The Event Dates would then be queried for the particular season. Hope that helps.

 

[roberemok]

Thanks again for your opinions. Based on my own design plus some of your ideas I now have this. My final problem is trying to figure out how to keep the history of a franchise if it moves leagues, divisions or renames itself.

These are three problems I have yet to solve.

1) Franchise moves but I want to keep their entire history.

Montreal Expos become expansion team in 1967 and becomes the Washington Nationals in 2005

2) Franchise does not move but renames themselves. I want to keep their historical name for seasons past.

Anaheim Angels till 2004 and rename themselves to the Los Angeles Angles of Anaheim

3) Franchise moves leagues and divisions but I want to know what league and division they were in past seasons.

Milwaukee Brewers move from the AL East to the NL Central in 1997

Any suggestions/ideas would be a huge help.

Thanks
Rob