Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
add comment

1 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.

share|improve this answer
    
also the URL's should probably be moved to another table too depending on what they signify. –  Chris Travers Mar 18 '13 at 2:13
    
@ChrisTravers - I thought about that too, but I went with the assumption that URL has a language element. Still, if that were easily predictable (like a specific query string component) then for sure it would be better to normalize it out. –  Joel Brown Mar 18 '13 at 10:18
    
Image we can change language in user settings, and like facebook we only use one domain for all logged users (facebook.com). I wonder have they solved this ? Bcs I use english version but from time to time I write messages in diffrent languages (polish etc.). I think that checking in which language is message is too complicated to implement! –  Thomas Mar 18 '13 at 16:03
    
@Thomas - There could be an important difference between things you want to publish, e.g. product information, and things that your users (or third parties) supply to your systems, like messages. You can control what you publish but you can't force users to pick a language, or even to use just one, even in a single message. Unless you plan on doing a machine translation of user messages they will only ever be in the language the user chooses anyway. For user-supplied data you may be better off just ignoring the language. It will be what it will be. –  Joel Brown Mar 18 '13 at 17:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.