CODEWITHSUNDEEP
mysqldatabase-designinternationalization
Yang
Yang

Reputation: 8701

Building a site with complicated internalization support

When a user browses some page and then switches a language, then the following actions should occur:

So for example, a user browses /about-us page then he switched to German language, then he should be redirected to /uber-uns if a translation of /about-us in german exists.

Currently I have a structure like this,

CREATE TABLE `pages` (

  `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT
  `language` varchar(3),
  `urlSegment` varchar(250), 
  `content` TEXT

) DEFAULT CHARSET=UTF8;

What relationships/columns should be added to achieve that?

Upvotes: 4

Views: 216

Answers (1)

dened
dened

Reputation: 4310

One page can have multiple translations, so you need one-to-many relationship schema:

CREATE TABLE pages (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE page_translations (

  page_id INT NOT NULL
  language varchar(3),
  url_segment varchar(250) UNIQUE,
  content TEXT,
  FOREIGN KEY (page_id) REFERENCES pages(id),
  PRIMARY KEY(page_id,language)

) DEFAULT CHARSET=UTF8;

In the pages table you can store some useful data related to all translations of the page.

Then, knowing url_segment of the current page, you can easily get the url_segment of this page in another language:

SELECT new.url_segment
FROM page_translations AS new,
     page_translations AS cur
WHERE new.page_id = cur.page_id
  AND cur.url_segment = '/about-us'
  AND new.language = 'de'

Upvotes: 3

Related Questions