Old Databases

Earlier today I stumbled across an older post while looking for something else and noticed that there is no footnote defined for the 1 in the first sentence. At first I thought this was something that might not have come over during the data migration that converted posts from the v4 database to the very different v5 structure. However, after looking at the last backup from v4, I can see this problem dates back even further: to the time when v2 switched over to v4.

That was in January 2016.

The footnotes were eventually found in the last backup of the v2 database as rendered HTML, telling me that the source of the post was most likely Evernote. Sure enough, all the requisite metadata was there showing that the post did originate from Evernote back in 2012 and it's been sitting untouched ever since.

This blogging engine has seen quite a bit of change over the decade that it has existed, going from an Evernote-linked tool to something that could accept posts from multiple sources to something that could host podcasts to the something that currently exists. Each major revision has seen the database schema change quite a bit as past lessons guide future directions. So, as this was the first time in several years that I've looked at the 10Cv2 database, what does the 40 year old me think of the design?

Well … it wasn't very sophisticated. Heck, I'd say it was downright barbaric in its design, which leaned heavily on the Evernote API structure. The only way it could ever be performant for more than a few dozen sites would be to have the contents of every table kept in memory at all times, which is not recommended when running any version MySQL created since the Big Bang1. This lack of regard for previous efforts is not necessarily a bad thing, though. Working within the constraints of various shared hosting servers, then later VMs on Sakura and Amazon, I learned how to design databases that can run on very basic servers. The systems I make now, even if they're destined for powerful enterprise-grade server hardware, are designed to be as quick and nimble as possible while being flexible enough to allow for and endless amount of evolution going forward. Software projects are never finished, after all; they're abandoned. The early versions of 10C certainly prove the case.

I wonder what a future version of me would think of the v5 data schema.

Over the next few days I'll dedicate the time to restore these old footnotes so that posts from 2012 and earlier are properly structured. Hopefully there won't be too many items that need more than a straight update. The data structures have certainly evolved over the years, but the Markdown syntax that is used for Footnotes has remained incredibly consistent since I ripped it off from a WordPress plugin2 I had been using previously for the very same reason. With any luck, this will be the last time I have to go back to the v2 database to look for missing data.


  1. Yes. That Big Bang. Using the MEMORY engine is not recommended without a whole lot of failsafes put into place right from the onset and, even then, it's better to not use it.

  2. Many thanks to John Watson for creating FD Footnotes for WordPress. While the footnote implementation in 10C has evolved over the years to support various new requirements, the core functionality that went into Noteworthy, then later 10Cv2 and v4 was taken from John's plugin.