Designing the UI localization of Nickelpinch
Dec 10th 2012(Cross-posted on Nickelpinch.org)
One feature of Nickelpinch that is critical is the multi-language support. Not just for the descriptions entered in for purchases, etc, but the actual UI itself. In coming up with the MySQL schema to handle this, it ended up breaking into two areas:
Term Information
When accessing a specific term; I wanted it to be based on term, not some ID. When building the view, I want to have lines like: $term['amount']. I ended up with three tables to support this.
- locale
- locale_id
- value - English, Deutch, etc
- terms
- terms_id
- value - In English; 'amount', 'description'
- rosetta
- rosetta_id
- locale_id
- terms_id
- value - Deutch for amount, description, etc
The locale table stores all the possible language options. The terms table stores all the terms used in the UI, and the value is in English. These will eventually make up the 'key' that all terms will be referenced as in the views. The rosetta table is the pairing of them; with that we can ask "Get me the term in a specific language".
Page Information
When querying for all the terms for a specific page, I needed terms to be grouped by the pages they're on. I could have built queries for each page, but it made more sense to build in the idea of page and page_groups.
- page
- page_id
- value - The page name; think 'add_entry', 'overview'
- page_group - Group all terms to a page, for easy pulling
- page_group_id
- page_id
- terms_id
The page table is just the shortish list of all the pages/views in Nickelpinch. The page_group table is what stores the grouping of terms to a specific page.
What I ended up building is a great relational model that can handle any number of pages, terms and languages, all the while having strong normalization. I can make just one query for all the pages UI component, and just need to give it the page I'm looking for (page.value) and the language I need the terms in (locale.locale_id). With a couple JOINs in there of course. :)