Creation of a site with complex support for internalization

Question

When a user views a page and then switches the language, then the following actions should be performed:

• If there is a translation for the page that he is viewing now, go to this page.
• If there is no translated version of this page, go to the main page.

So, for example, the user browses the page /about-us, then switches to German, then he should be redirected to /uber-uns, if there is a translation /about-usinto German.

I currently have such a structure,

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 this?

Answer 1

, " ":

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;

pages , .

, url_segment , url_segment :

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'