Re: Lazy summer project: build a basketball app.

kvnkrkptrck_at_gmail.com wrote:

> All, it's been a long time since I've done a from scratch database
> design. What follows is a shameless appeal for anyone with some
> database design expertise, a few extra minutes, and desire to help out
> - to offer any criticisms/advice they see fit. I'll conclude the
> opening paragraph with an apology to those without said expertise, few
> extra minutes, or desire to help; for the time wasted on reading the
> opening paragraph (and a suggestion to read no further/waste no further
> time).
>
> My dad is a huge D3 college basketball fan, and during the season, he
> attempts to keep daily tabs on the entire division (hundreds of teams).
> Last year, he approached me for help. He'd been using dozens of Excel
> sheets to track the games, and was having tremendous difficulties
> keeping up. Data entry was a nightmare - all games needed to be
> modified in two places (once for each team). Due to lack of data
> integrity, his sheets were riddled with errors/typos. And reporting
> was even worse - he spent hours each weekend manually sorting sheets,
> summing functions, etc., in order to answer such simple questions as
> "Which team has the best record", and "Next weekend, are there any
> undefeated teams playing each other".
>
> Anyway, at the time, I helped him out by writing some ugly Excel macros
> for data synchronization (allowing him to enter a score once and have
> it posted in both places) - but it was conditioned on his promise to
> let me design a relational database for the next season.
>
> So here I sit - it's been a long time since I've done a from-scratch
> relational design, but it's a slow afternoon, so I figured I'd start
> some ER planning.
>
> I start with a Team entity, with each team having a division attribute
> (1, 2, or 3), a conference attribute, a college name, a shorthand name
> / abbreviation, and a city/state (pk will be division, conference,
> college name). I can hand this entity over to my dad right now, and he
> can start populating it. But I'm getting a bit stuck on the next step.
>
>
> At first, I thought, I'll need a simple Game relation. But I think
> this will need to be a super relation of sorts - because certain
> attributes of tournament games are different than non-tournament games
> (e.g. existence of a home/away team; existence of the name of the
> tournament; a tournament round number; etc.).
>
> Another odd characteristic of the Game relation is that I can't come up
> with a decent natural key while retaining symmetry of opponents (team A
> vs. team B == team B vs. team A).
> If I recall correctly, in a game between some team1 and team2, both
> (Game Date, Team1) and (Game Date, Team2) are viable candidates - no
> team will ever play two games on the same day. But I'd have to break
> the symmetry in order to get a natural key (I think).
>
> So, I'm now leaning towards introducing an artificial Game entity with
> a surrogate Game_id, e.g. <Game_id(pk), scheduled date>, and also
> introducing a Opponent relation <Game ID (pk / fk on Game), Team (pk /
> fk on Team), Points scored, ...>. I'd have to enforce some
> application-level constraints (e.g. each game_id must exist exactly two
> times in Opponent (if at all)), but it does keep a nice symmetry.
>
> Anyway, I've got ample time to think this one through - and my thought
> is "if I build this right the first time, I can design some front-ends
> for data loading, some views for data extraction, etc..., and my dad
> might have a nice toy to play with for years to come".
>
> So, having said that - I'm open to any design advice or criticisms
> anyone may have to offer. My best idea right now:
> Team <division, conference, college, a shorthand name, city, state>
> Game <Game_id (pk), Date>
> TourneyGame <Game_id (pk - fk on Game.Game_id), Tournament_name,
> round_number>
> NonTourneyGame <Game_id (pk - fk on Game.Game_id)>
> TourneyOpponent <Game_id (pk - fk on TourneyGame.game_id), Team_fk
> (pk), Points_Scored>
> NonTourneyOppenent <Game_id (pk - fk on NonTourneyGame.game_id),
> Team_fk (pk), Home_Away_Ind, Points_scored>

What about forfeits? Or does college basketball not have forfeits?

Do all teams have a home court? If so, when a tournament game is played at the home court of one of the teams, is that team not a home team? Is a team ever considered the home team when playing at a different court?

Does college basketball have anything like a pre-season or exhibition game?

What are the requirements your father has? Received on Thu Jun 29 2006 - 21:59:05 CEST