So I have 1 Content table for storing all system text. (it is a user content website)
content_id, Keyword, en, fr, ea, sp, ........ (each of these columns refers to a language ID from the language lookup table).
This is fine upto here, but now how do I translate the data stored in other tables - in the lookup tables such as City names, Gender, drop down lists data, etc? If I copy them to the content table then how to maintain the relation between the various lookup tables and the content table without having to create individual relations as there are over 100 lookup tables.
Secondly, for user content it is tricky. User content varies between status updates, photo descriptions, wall posts, reviews, discussions, etc. Each object has it's own table. To maintain language translation for each, do we add colunm like en, fr, ea, .... for each object table? Languages are session based and I am supporting 20 languages for launch with more to be added as website grows. Platform is PHP & MySQL.