When I started Tabulas, I literally knew nothing about best programming practices. All I was interested in was to see how hard it would be make something that was better than Xanga and LiveJournal. Looking back, that naivete about writing webapps was a really good thing in keeping me focused (nowadays, all my "knowledge" about the potential pitfalls about writing/deploying a webapp really hinder my abilities to start up new projects).

So, over the past few years, I've been slowly phasing out "bad" code (written back in '04 when I knew nothing!) in favor of "good" code (code which still has a shelf-life of maybe another six months before I refactor it again). Last week, I finally disabled http://my.tabulas.com/, the old control panel. That means all users are now using the new control panel; coupled with the new front-end, this is finally the Tabulas 2.0 I wrote about back in 2004/2005 (if you're keeping count, this was the third 2.0 version that was promised). (The next step would be to start implementing the promised 2.1 features)

Now that I've finally consolidated the codebase to one single codepath (yes, it was running multiple copies all over the place before!), the next step is the database cleanup.

Right now, I have 96 tables; most which I don't know what they're doing. The naming schema for tables is all over the place, too. For example, the old users table:

CREATE TABLE `users` (
  `userid` mediumint(8) unsigned NOT NULL auto_increment,
  `username` varchar(16) NOT NULL default '',
  `user_pass` varchar(32) NOT NULL default '',
  `user_pass_temp` varchar(32) NOT NULL default '',
  `password` varchar(12) NOT NULL default '',
  `password2` varchar(32) NOT NULL default '',
  `email` varchar(64) NOT NULL default '',
  `joined` date NOT NULL default '0000-00-00',
  `code` varchar(11) NOT NULL default '0',
  `status` tinyint(1) NOT NULL default '0',
  `lastlogin` date NOT NULL default '0000-00-00',
  `totalFriends` smallint(5) unsigned NOT NULL default '0',
  `userHash` varchar(32) NOT NULL default '',
  `server` varchar(25) NOT NULL default 'jbiel.tabulas.com',
  `suspended` tinyint(1) NOT NULL default '0',
  `user_history` text NOT NULL,
  `user_cache` text NOT NULL,
  `user_option` text NOT NULL,
  `user_upgrade` tinyint(1) NOT NULL default '0',
  `user_ads` tinyint(1) NOT NULL default '0',
  `userStats` text NOT NULL,
  `userType` tinyint(1) NOT NULL default '0',
  `userHost` tinyint(1) NOT NULL default '0',
  `userSalt` varchar(32) NOT NULL default '',
  `userAccounts` text NOT NULL,
  `userIconId` int(10) unsigned default NULL,
  PRIMARY KEY  (`userid`),
  KEY `username` (`username`),
  KEY `email` (`email`)
)

Oh yeah. Notice the passwords duplication? (That was when I realized storing plaintext passwords was bad - don't worry, there hasn't been any data stored there for a while as I wiped them, but I left the columns active as I didn't want to dive into my old code and remove the column calls). Notice the complete lack of standard naming of the columns? Nice. (Not)

Fortunately, foreseeing this work item, all the current code goes through a layer of abstraction which lets me change the table/column names dynamically once in the code, so updating these tables/columns are relatively easy for me to do.

So today, I cleaned up the users table by naming everything consistently and dropping unused columns. How much data was redundant and unused?

root@host [~/mysql_backups]# ls -al tabulas.users*
-rw-r--r--  1 root root 18432778 Aug 24 21:11 tabulas.users.new.sql
-rw-r--r--  1 root root 26496439 Aug 24 20:53 tabulas.users.sql

... about 30%. God, I'm such a horrible developer. Here's the final structure:

CREATE TABLE `users` (
  `user_id` mediumint(8) unsigned NOT NULL auto_increment,
  `user_name` varchar(16) NOT NULL default '',
  `user_email` varchar(64) NOT NULL default '',
  `user_status` tinyint(1) NOT NULL default '0',
  `user_suspended` tinyint(1) NOT NULL default '0',
  `user_created` date NOT NULL default '0000-00-00',
  `user_updated` date NOT NULL default '0000-00-00',
  `user_host` tinyint(1) NOT NULL default '0',
  `user_salt` varchar(32) NOT NULL default '',
  `user_hash` varchar(32) NOT NULL default '',
  `user_pass` varchar(32) NOT NULL default '',
  `user_pass_temp` varchar(32) NOT NULL default '',
  `user_logins` text NOT NULL,
  `user_options` text NOT NULL,
  `user_icon_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`user_id`),
  KEY `user_name` (`user_name`),
  KEY `user_email` (`user_email`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 

Expect a lot more of these posts over the coming weeks as I tackle these changes bit by bit.

And if Tabulas is throwing any errors, let me know.