Drop-down menu look-ups for multiple languages
Posted: Mon Mar 05, 2012 2:28 pm
Hi,
I'm currently developing a web application using PHP/MySQL that allows users to schedule different types of events - as an example, when they go to the "schedule event" page they just have to choose the date and select an option from the "event type" list relating to the event they're holding, and the selection options for this list are populated from the database.
At the moment I'm only supporting English but need to translate the drop-down menu options into Spanish; which language is used will be determined by a setting in the user's account options (which is held as a $_SESSION value for quick access) but the IDs for these options will be exactly the same and on a 1-to-1 basis, so that there will be the same number of options irrespective of language and they all mean the same thing.
I'd like to implement as much of this in the database as possible, but my design theory is getting a bit fuzzy at this point - is it sensible to have all of the values held in the field the "event" relates to, so the schema might look like
TABLE: EventOptions
id
desc_EN
desc_SP
sort_order
and the query in the PHP script then just needs to select the correct field (or all of them if we're being lazy) and displays the correct one based on the user's language.
One limitation of this approach seems to be that the database schema needs to be updated each time a new language is added (as do the queries in the scripts) so would having something like a look-up table for the look-up tables be more appropriate?
Thanks in advance
Mecha Godzilla
I'm currently developing a web application using PHP/MySQL that allows users to schedule different types of events - as an example, when they go to the "schedule event" page they just have to choose the date and select an option from the "event type" list relating to the event they're holding, and the selection options for this list are populated from the database.
At the moment I'm only supporting English but need to translate the drop-down menu options into Spanish; which language is used will be determined by a setting in the user's account options (which is held as a $_SESSION value for quick access) but the IDs for these options will be exactly the same and on a 1-to-1 basis, so that there will be the same number of options irrespective of language and they all mean the same thing.
I'd like to implement as much of this in the database as possible, but my design theory is getting a bit fuzzy at this point - is it sensible to have all of the values held in the field the "event" relates to, so the schema might look like
TABLE: EventOptions
id
desc_EN
desc_SP
sort_order
and the query in the PHP script then just needs to select the correct field (or all of them if we're being lazy) and displays the correct one based on the user's language.
One limitation of this approach seems to be that the database schema needs to be updated each time a new language is added (as do the queries in the scripts) so would having something like a look-up table for the look-up tables be more appropriate?
Thanks in advance
Mecha Godzilla