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.
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:
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?