I need to implement multi language system in an existing system that was not developed for this. The number of entries in the tables that will hold data that has to be translated will not exceed 5 digits and more importantly the number of results per page should be limited to a few dozen at most.

So I came up with this table to house additional languages for everything. I'm planning to alter the code that selects the data and use a JOIN to get the appropriate translation for the fields in question.

Will this cause me problems and is there a better way that will not take much more time to implement as there is a time constraint on this.

CREATE TABLE `translations` (
    `row` BIGINT(20) NOT NULL,
    `field` VARCHAR(50) NOT NULL,
    `table` VARCHAR(50) NOT NULL,
    `language` INT(11) NOT NULL,
    `value` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `row_field_table_lang` (`row`, `field`, `table`, `language`),
    INDEX `entry` (`row`),
    INDEX `field_table` (`field`, `table`)

SELECT IFNULL(`t1`.`value`, `c`.`name`) AS `name`, IFNULL(`t2`.`value`, `c`.`text`) AS `text` 
  FROM `entries` AS `c` 
    LEFT JOIN `translations` AS `t1` 
       ON `t1`.`row` = `c`.`id` AND `t1`.`field` = 'name' AND `t1`.`table` = 'entries' AND `t1`.`language` = 2 
    LEFT JOIN `translations` AS `t2` 
       ON `t2`.`row` = `c`.`id` AND `t2`.`field` = 'text' AND `t2`.`table` = 'entries' AND `t2`.`language` = 2 
  • From storing the Column Headers in the local language it looks good. How are you planning on getting the headers attached to the right columns in the output data. The best way may be in the UI layer to have 2 selects from the database then code to put the headers in the right display place. – RubberChickenLeader Mar 12 '14 at 12:40
  • I added the select query I experimented with. – transilvlad Mar 12 '14 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.