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` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `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`) ) COLLATE='utf8_general_ci' ENGINE=MyISAM; 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 ;