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:
- Find all trade proposals involving either team
- Get all players involved in said trade proposals
- Check if any of the players are involved in the just accepted trade
- 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.
