Re: Lazy summer project: build a basketball app.
Date: 30 Jun 2006 09:15:42 -0700
J M Davitt wrote:
> 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>
> Good problem.
> I think you should have something for Venue, as well; some of the
> Tourney/NonTourney stuff should fall away and it would avoid
> confusion between conference and non-conference games when two
> teams in the same conference meet in a non-conference tournament.
> If anything, I think that games Scheduled and Played should be
> different. And I'm not sure you need a Game_id; they always have
> a way of deciding who will wear home/away uniforms -- use that.
Wow - you've just made clear that I have quite a few questions to ask him.
> Tournaments are different, too: we know where and when they'll be
> played and how many games will be played -- we just don't know what
> teams will be seeded. So, I think some operation is necessary to
> Schedule tournament games.
Agreed - from what I can tell, he would "schedule" a tournament game by pitting the team up against a "null" opponent on the tournament start date... then fill in the opponent and scores and second/thrid round games as the tournamant progressed. This method worked well engouh, but it has shortcomings. If modeled properly, it should be easy to give him a "tournament setup" form, allowing him to add teams to particular tournaments; and a "tournament view" form which displays the round by round outcome of a given tournament. But I definitely need to verify that he is interested in these types of questions (if he isn't, then tracking rounds of a tournament may wind up being a hassel, if all he cares about is tracking the wins/losses of the teams).
> What are you going to implement with? (And, do you hate all vowels,
> or just a, e, and i?)
I'll probably go with Access, just because it is already installed on both his and my home machines (and, as he puts it, "not that it matters, but I also happen to have Access at work - wink wink, nudge nudge"). I've never worked with Access before, but I'm not planning on spending too much time on the GUI crap. My intent was to create base tables and a few updatable views, then build basic forms on those views. I'll send him queries for any questions he needs to ask. But I suppose this, too, will be informed by asking some pointed questions this weekend. Received on Fri Jun 30 2006 - 18:15:42 CEST