The Solution? More Tables

I don’t know very much about database design.  I tried taking a course once in college but the professor scared me on the first class and I ended up dropping it, which I guess now that I write it out I regret.  What little I do know I have taught myself from both production examples from my previous jobs and just general experience.  That being said, there is one  rule that I’m slowly learning that I feel the need to record.  Whenever there is some sort of complication with storing or retrieving data in a relational database, the solution is always more tables.

Yesterday I was struggling with a problem.  If two teams agree to a trade involving a given set of players, all other trade proposals including those players should no longer be active.  For example, if Albert Pujols is involved in a trade that has been accepted, he shouldn’t be still out there in other trade proposals – we don’t want two or more teams concurrently accepting trades with Albert Pujols!

On its face, this seems like a basic enough problem, but the twist is my existing database schema.  It looks like this:
[cc lang=”mysql”](
ID int(11) NOT NULL auto_increment,
ProposalDate date NOT NULL,
ProposingTeam tinyint(4) NOT NULL,
TargetTeam tinyint(4) NOT NULL,
ProposingTeamPlayers varchar(30) NOT NULL,
TargetTeamPlayers varchar(30) NOT NULL,
ProposingTeamLoan int(11) NOT NULL,
TargetTeamLoan int(11) NOT NULL,
Accepted tinyint(4) NOT NULL default ‘0’,
Rejected tinyint(4) NOT NULL default ‘0’,
)[/cc]The problem here is that I’m storing “ProposingTeamPlayers” and “TargetTeamPlayers” as strings. So for each trade, I implode the array of players involved into a comma-delimited list. This is all well and good until you realize you want to search for all trade proposals involving a given player. The process then becomes:

  1. Find all trade proposals involving either team
  2. Get all players involved in said trade proposals
  3. Check if any of the players are involved in the just accepted trade
  4. If yes, then mark that trade proposal as rejected (which, implicitly, it is)

The solution to getting rid of this awful, slow code? Add a new table:[cc lang=”mysql”]TradeProposalID int(11) NOT NULL,
PlayerID int(11) NOT NULL,
TeamID int(11) NOT NULL,
PRIMARY KEY (TradeProposalID,PlayerID)[/cc]Then this query gives us what we want:[cc lang=”php”]$sql = “SELECT ID FROM TradeProposalsIndexTable JOIN TradeProposalsPlayersTable ON ID=TradeProposalID WHERE Accepted=’0′ AND Rejected=’0′ AND PlayerID=$playerID”;[/cc]Mark all those trades as rejected, and all done.

Without adding new tables, I had a mess trying to store multi-player trades in my database, and I had a hacky solution that clearly was not well thought out.  Add a table, remove those ProposingTeamPlayers and TargetTeamPlayers columns, and a multi-line complete mess of a solution turns into 2 elegant lines of SQL.

So we’re left with a simple rule to follow at all times: if you are getting confused by how to get or store data with your relational database, add some more tables.

First Pitch

We are now 4 days back from First Pitch: Arizona.  I can’t speak for Geoff, but for me, the conference was a rousing success.  If you had told me all the positive things that happened in Phoenix were going to happen before going, I would have gladly paid twice.  An overview:

  • Maybe not the father of fantasy baseball, but at least a pretty influential uncle, Ron Shandler is the real deal when talking about one of the game’s great minds.  He presented a new approach to fantasy baseball at the conference, one which is similar to my game in a number of ways.  We met (along with Geoff!) for a hour after he presented his game, and he came away with a very favorable impression of what I’ve been working on.  He even mentioned it as legit to the entire conference.  If that isn’t a strong endorsement, I don’t know what is.
  • Joel Henard at Baseball Prospectus Radio offered to interview me for his show.  Repeat: I was offered an interview.  About a fantasy baseball game.  From the guy who has interviewed real, actual GMs and baseball players.
  • Everyone, without exception, who got a taste of my full pitch was asking me things varied from “sounds awesome” to “when can I sign up?”
  • Joe Sheehan recognized me coming up an elevator and asked if I’d be around later at the Rising Stars baseball game or for poker later on.  Swoon.

The amazing thing about having people, real live people validate what you’re spending hours a day working on – what you’ve left a safe, comfortable job, to work on – is that it makes you forget everything else.  It just makes you want to f’n code the hell out of it.  So, I’ll say it again: let’s go.

What I’ve been up to, part 2


Fly balls:

Ground balls (even though the OF looks silly here I think it makes sense – there really are three dead spots that sorta blend together on the fringes, one behind each defender):

Line drives:

Pop ups (no, I’m not changing the name of the Y-axis):


Fly balls:

Ground balls:

Line drives:

Pop ups:

I really should merge pop ups with fly balls, but for now just merge them in your mind. Also, if you look really closely you’ll notice that fly balls and line drives extend out to 480 ft in the y direction, while ground balls and pop ups only go to 420. I’m going to re-run everything tonight, extending out to 500 ft (but probably won’t share those, unless someone asks).


Much like Geoff, I haven’t checked in over here in far too long.  The last time I posted, I mentioned that I had given notice at my previous job and was going full time on the project that most of you know about already.  So it seems like that is as good a place as any to start.

While Geoff is working on an extreme research project, I am working on almost the opposite: a new consumer-facing fantasy sports service, ottoneu.  In the vein of Yahoo! Fantasy Sports, ottoneu will provide fantasy games for users, but with an emphasis on niche users and in-depth, hardcore games.  Without getting in to the details (and blowing any fun surprises that might be coming down the line), I can safely say a few things.

  1. The fantasy baseball product I am working on is unique and fun
  2. Working for yourself is fun and stressful
  3. It is incredibly rewarding to spend 100% of the day working on something that you are not only interested and invested in, but you truly enjoy

So I have leapt.  I am now the Founder and CEO of ottoneu, Inc.  Soon, I will be bringing you better fantasy sports – richer, more interesting, more engaging, and much, much more fun.  My goal here is to, without ruining any good surprises, take you along on the ride to launch day.