Man, I forgot about this place. No time for apologies, let’s get into it.

It is February 19, 2014. There is a high likelihood of a lot of new users coming to ottoneu in the next month before the season starts, but let’s look at where things are now, shall we?

1. 1917 active teams.
2. 25 active prize leagues and 123 active non-prize leagues.
3. 401,293 player transactions, spending $538,395 in ottoneu cap.

I’m going to throw a parade when we get over $1mil in cap spent. There are a few other important things to note, especially in regards to the now woefully outdated first 30 days post. My users remain incredibly patient and smart and understanding. The number of “shit is broken” emails have decreased to a trickle – ottoneu has become a relatively stable (KNOCK ON WOOD) platform! Even the auction drafts are performing smoothly, to the point where most of the emails about them are for really interesting feature enhancements, not “man this was slow, give me some money back.”

So, that is the basic state of things today. Now, what are the big things on the horizon? Well, there are 3 big initiatives this year, each worthy of its own series of posts:

1. Rewriting the stats backend – after determining that a soft innings cap (and thus allowing pitcher streaming on the last day of the season) was a double-edged sword for owners and self-balancing, there is a massive opportunity for performance improvements and general efficiency with ottoneu’s stats backend. This project is ongoing and should be wrapping up in the next couple of weeks. It should be completely transparent to users, and it has to get done before the season starts. So, that’s priority #1.
2. A better lineup page experience – the lineups page is fine, it gets the job done. But during the long baseball season, this is where a lot of time is spent. It also is the basis for the off-season roster organizer. More stats, splits, and a smoother experience swapping players in and out will go a long way towards making ottoneu feel world-class.
3. A full redesign – while I may have sentimental attachment to the Waste Management colors and my crazy-good (read: not) logo, I think ottoneu can look significantly better. This project is actually really close to being done, but probably won’t be released until the All-Star break, just to tighten everything up and not get in the way of the top two priorities. The new logo looks boss.

That is where things stand going into this season. I plan on keeping a loose development blog here, so feel free to comment here, tweet at me, hit me up on Facebook, or just plain email me (help at ottoneu dot com). I cannot wait for baseball. Good luck this season!

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’,
PRIMARY KEY (ID)
)[/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.