David King

full stack developer

All Blog Articles

Prev « A nice search solution and interface :-) Next » Automatically backup MySQL to Amazon S3 (and a dog)


Refactoring an old database for multilang support

14 Mar 2012

I've recently had the opportunity to revisit and re-factor a database that I wrote designed a decade ago; it's still up-and-running with heavy traffic however it's become difficult for the current developer to maintain (hey Dad) and the client is looking to add a multi-language slant.

EER diagram showing non-relational database with no scope for i18n support
the old, non-relational database
Refactored EER diagram showing fully-relational database with scope for i18n support
the new relational database + multilang translations

Table-wise we decided to keep with pretty much the same to make the transition painless, however when you compare the original and the new you can see everything has more structure and logic:

Simplified EER to show only the i18n parts of the diagram
structure on the top, content on the bottom

With this structure we can do some pretty nifty queries that pull out the language of choice but fall-back to English on a per-column-basis, observe:

SELECT
	u.id,
	COALESCE(lang1.title,     lang2.title)      AS title,
	COALESCE(lang1.body,      lang2.body)       AS body,
	COALESCE(lang1.voiceover, lang2.voiceover)  AS voiceover,
	COALESCE(lang1.handout,   lang2.handout)    AS handout
FROM `units` AS u
	LEFT JOIN units_lang AS lang1 ON lang1.unit = u.id AND lang1.lang = 'zh'
	LEFT JOIN units_lang AS lang2 ON lang2.unit = u.id AND lang2.lang = 'en'
WHERE u.id = 32;

In this statement:

Pretty nifty huh?


Prev « A nice search solution and interface :-) Next » Automatically backup MySQL to Amazon S3 (and a dog)