Multiligual database design

Question

I assume that the best approach for many languages (5 and more) is concept based on:

    CREATE TABLE `language` (
    `language_id` char(2) NOT NULL,
    `collation` varchar(64) NOT NULL,
    PRIMARY KEY (`language_id`)
);

CREATE TABLE `product` (
    `product_id` int(11) NOT NULL auto_increment,
    PRIMARY KEY (`product_id`)
);

CREATE TABLE `product_translation` (
    `product_id` int(11) NOT NULL,
    `language_id` char(2) NOT NULL,
    `group_id` int(11) NOT NULL,
    `name` varchar(50) NOT NULL,
    `url` varchar(50) NOT NULL,
    `price` decimal(9, 2) NOT NULL,
    `description` text NOT NULL,
    UNIQUE KEY `language_id_2` (`language_id`, `url`),
    KEY `language_id` (`language_id`, `group_id`, `name`),
    FULLTEXT KEY `name` (`name`, `description`),
    FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`),
    FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`),
    PRIMARY KEY (`product_id`, `language_id`)
);

But what if content is filled by user? Should this structure be used only for static content ? Registred user can at any time change his language settings also the data that he is adding (private messages,articles) can be added in any language we cannot force him to add only english content. Should all data be stored in one table and column (body), am I right ?

For example private messages:

    CREATE TABLE `msg_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thread_id` int(11) NOT NULL,
  `body` text NOT NULL,
  `priority` int(2) NOT NULL DEFAULT '0',
  `sender_id` int(11) NOT NULL,
  `cdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Answer 1

You can indicate what language a message body is in, assuming that you know what language it is because the user told you so or because you have a process that reads the message and guesses the language. Therefore your message table could look something like this:

CREATE TABLE `msg_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thread_id` int(11) NOT NULL,
  `body` text NOT NULL,
  `priority` int(2) NOT NULL DEFAULT '0',
  `sender_id` int(11) NOT NULL,
  `cdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `language_id` char(2) NOT NULL,
  FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

NOTE: Unless you plan on charging a different price in different languages, you don't want to keep the price in the product_translation table. If you want to differentiate the price based on markets then you are better to split price into its own intersection table between product and whatever is suitable, like country or region or currency.