I'm assuming you don't want to use something like google translate, but only use translations that have been 'approved'
I think you will need tables along the following lines
tblLanguages
LanguagePK autonumber primary key
Language text
tblPhrases
LanguageFK long indexed, duplicates OK}multiple index with Phrasecode as primary key
PhraseCode long indexed, duplicates OK}
Phrase text or memo indexed, no duplicates
The phrasecode would be the same for the same sentence in each language
a query to find a translation for a specific sentence would be something like
Code:
SELECT T.phrase
FROM tblPhrases as O INNER JOIN tblPhrases as T ON O.phrasecode=T.phrasecode
WHERE O.LanguageFK=1 AND T.LanguageFK=2 AND O.Phrase='100% Cotton'
this will cover your for as many languages as you wish and resolves 3 of your 4 requirements.
with regards
Be easily usable by someone who does not have a database background.
this will be down to you as the developer to provide easy to understand and use forms and reports.
Other thoughts:
- not clear from your description, but is it possible that the context of a sentence and therefore its translation can vary depending on the paragraph in which it is located?
- you will need to investigate issues with character sets for different languages
- how to split the current text and translation into matching component sentences/phrases
- how to present to your translators a partially translated document