CREATE TABLE `languages` (
    `lang_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `lang_name` VARCHAR(60) NOT NULL
);

CREATE TABLE `post_translations` (
    `post_translation_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRI
+MARY KEY,
    `post_translation_cat` INT(11) UNSIGNED NOT NULL,
    `post_translation_date` DATETIME NOT NULL,
    `post_translation_title` VARCHAR(255) NOT NULL,
    `post_translation_body` TEXT NOT NULL,
    `post_translation_lang` TINYINT UNSIGNED NOT NULL
);

CREATE TABLE `posts` (
    `post_id`, INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `post_translation_id` INT(11) UNSIGNED NOT NULL,
);
[download]

For each post, you'll have multiple translations. All you have to do now is to find the correct translation for the current post_id.

I could not understand how this works? would you mind explain it to me?

How would I use the `post` table with an auto_increment field `post_id` and only 1 field `post_translation_id` to have more than one translation per post?

Thanks for your help

If someone wants to read post $post_id in language $language_name,

my $sth = $dbh->prepare('
   SELECT *
     FROM post_translations
     JOIN posts
       ON post_translations.post_translation_id = posts.post_translati
+on_id
     JOIN languages
       ON post_translations.lang_id = languages.lang_id
    WHERE posts.post_id = ?
      AND languages.lang_name = ?
');

$sth->execute($post_id, $language_name);
[download]

Pardon any syntax errors.

How would I use the `post` table with an auto_increment field `post_id` and only 1 field `post_translation_id` to have more than one translation per post?

You woulnd't. You'd fix my bug :)

If you wanted to have it autoincrement,

CREATE TABLE `languages` (
    `lang_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `lang_name` VARCHAR(60) NOT NULL
);

CREATE TABLE `post_translations` (
    `post_translation_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRI
+MARY KEY,
    `post_translation_cat` INT(11) UNSIGNED NOT NULL,
    `post_translation_date` DATETIME NOT NULL,
    `post_translation_title` VARCHAR(255) NOT NULL,
    `post_translation_body` TEXT NOT NULL,
    `post_translation_lang` TINYINT UNSIGNED NOT NULL
);

CREATE TABLE `posts` (
    `post_id`, INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
);

CREATE TABLE `post_to_translation` (
    `post_id`, INT(11) UNSIGNED NOT NULL,
    `post_translation_id` INT(11) UNSIGNED NOT NULL,
);
[download]

Update: Since it's an 1:N and not N:N relationship, post_to_translation is not necessary.

CREATE TABLE `languages` (
    `lang_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `lang_name` VARCHAR(60) NOT NULL
);

CREATE TABLE `post_translations` (
    `post_id`, INT(11) UNSIGNED NOT NULL,
    `post_translation_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRI
+MARY KEY,
    `post_translation_cat` INT(11) UNSIGNED NOT NULL,
    `post_translation_date` DATETIME NOT NULL,
    `post_translation_title` VARCHAR(255) NOT NULL,
    `post_translation_body` TEXT NOT NULL,
    `post_translation_lang` TINYINT UNSIGNED NOT NULL
);

CREATE TABLE `posts` (
    `post_id`, INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
);
[download]

ikegami ++!

this is the best way for multi-language design.




---

---

I am trying to improve my English skills, if you see a mistake please feel free to reply or /msg me a correction

BTW, I heartily recommend:

• Database Design for Mere Mortals, 2nd Ed
• SQL Queries for Mere Mortals, 2nd Ed
• The Practical SQL Handbook, 4th Ed

And, for future reference, the search term you are searching for is junction table.

You got good advice from ikegami already. I just want to add, since we're OT, that I find this naming convention-

CREATE TABLE `posts` (
    `post_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `post_cat` INT(11) UNSIGNED NOT NULL,
...
[download]

-completely annoying. It's like naming a hash like so %hash_variable_name. Redundant information is almost always suboptimal if not an outright mistake in code design. I have no idea how it's become popular in schema design. I'd rather see cat spelled out category or catalog or catamaran or whatever you're assuming everyone will know it's short for than see something like posts.post_cat.

I've seen too many people using 'cat' as a short cut for 'category'

About the naming convention ... I think it's used that way so you don't get confused or type too much when selecting from multiple tables ... i.e: you don't have to say posts.post_id ... otherwise if you're selecting from 2 tables that have the same field name you'll have to do something like posts.id and if you want to use it somewhere you'll have to provide a temporary name using 'As `fields`'

At least that's what I remember from my readings.

How is posts.id more typing or more confusing than posts.post_id?

I thought about this too ... but it will be difficult to add new languages and if you have too many fields the table will look messy.

I want to make it as dynamic as possible ... that's why I am trying to find a solution that works but I am not expert in db design