UPDATE 2: I've actually ended up using this, and it's great after a couple tweaks. Here's my post on its actual design, and in action: http://tim.hithlonde.com/2013/lemon-schema-works/
I am building a web app, and I want it to support multiple languages. This structure has two components:
- Connecting locale ('english','Deutch', etc) with terms, and having a rosetta stone connecting terms, and terms in specific language.
- Grouping terms by page. I don't want to say, SELECT term1,term2,etc through the 30+ terms I might need on a page. I want to ask by the page they're connected to.
Here is my proposed table structure (note all the id's have relationships/indexes among them to make very efficient queries):
* locale * id * value //English, Deutch, etc// * terms * id * value //In English// * page * id * value //Think add entry, menu// * page_group //group all terms to a page, for easy pulling// * id * page.id * term.id * rosetta * id * locale.id * term.id * value //french word for amount, description, etc//
This will allow queries like:
SELECT localization.value, terms.value FROM localization INNER JOIN terms ON terms.id=localization.termid INNER JOIN page_group ON page_group.termid=localization.termid INNER JOIN page ON page.id=page_group.pageid INNER JOIN locale ON locale.id=localization.localeid WHERE page.value='add_entry' AND locale.id=custlangid ORDER BY terms.id
I only have to ask for two items; the language id that I need, and the page I need. It'll serve up all the terms, in the specified language, that are a part of the group of terms for that page.
I think this is a really good structure, but I would love some feedback.
UPDATE: To clarify, we are just talking about localization of the UI components. (labels, navigation, helpful text) All the info the user enters will be stored in unicode, not in this schema.
UPDATE 2: I've actually ended up using this, and it's great. Here's my post on it's actual design, and in action: http://tim.hithlonde.com/2013/lemon-schema-works/