Optimal database model for multilanguage websites

Question

My old database designs was looking like

 id | name_en | title_en | name_ru | title_ru ...

I was searching for optimal database structure for multilingual websites where I can add remove languages, posts ... etc without changing database structure quite a long time.

Finally created one. But I'm not sure if it's optimal and it has several fatal problems:

Language table - it's list of languages for whole application

-- ----------------------------
-- Table structure for Language
-- ----------------------------
DROP TABLE IF EXISTS `Language`;
CREATE TABLE `Language` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `iso` varchar(3) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `order` tinyint(3) NOT NULL DEFAULT '0',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

MenuType table - menu types like sidebar menu, top menu ...

-- ----------------------------
-- Table structure for MenuType
-- ----------------------------
DROP TABLE IF EXISTS `MenuType`;
CREATE TABLE `MenuType` (
  `id` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Menu table - All menu items, based on parent child structure.

-- ----------------------------
-- Table structure for Menu
-- ----------------------------
DROP TABLE IF EXISTS `Menu`;
CREATE TABLE `Menu` (
  `uid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id` int(11) unsigned DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `languageID` tinyint(3) unsigned DEFAULT NULL,
  `menuTypeID` tinyint(2) unsigned DEFAULT NULL,
  `order` int(2) DEFAULT NULL,
  `parent` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

Page table - multilingual pages table

-- ----------------------------
-- Table structure for Page
-- ----------------------------
DROP TABLE IF EXISTS `Page`;
CREATE TABLE `Page` (
  `uid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `languageID` tinyint(3) unsigned DEFAULT NULL,
  `content` text COLLATE utf8_unicode_ci,
  `deleted` tinyint(1) DEFAULT NULL,
  `permalink` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Well my design works like that: lets say our project works on 2 language. English (id 1 in language table) and Russian (id 2 in language table). Ever page has 2 records in table: like {uid - 1, id - 2 lang - 1 ...}; {uid - 2 , id - 2, lang - 2 ...}.

I think it will have serious problems because of repeating id's with foreign keys and programmatically will be difficult to maintain it. Any suggestions to fix it or any other design suggestions?

Please share your multilingual database design ideas.

I'm not experienced in databases and really need some rock solid database design for using long time in projects.

Need some db design that will work with multilingual seo friendly urls, multilingual posts. etc..

Thank you in advance.

Answer 1

You just have to change your key to be a combination of both the id of the page and the id of the language.

Answer 2

I don't know why you store the page content into database. Can the page content be changed through some kind of backend?

I only store language dependent content when it comes from user input. In that case I use the same database design as you.

If the page is static I load the strings from a ini file and then I used the values into a template.

The steps would be:

• Create the ini files with key, value pairs. One per language.

strings.en.txt:

title = Title
paragraph1 = This is the first paragraph.

strings.es.txt

title = Título
paragraph1 = Este es el primer párrafo

• Load the ini file according to user preferences/browser language (at first, you can get that with $_SERVER['HTTP_ACCEPT_LANGUAGE']).

$langs = parse_ini_file("strings.".$_SERVER['HTTP_ACCEPT_LANGUAGE'].".txt");

• Generate HTML replacing the strings with your variables. And in my php file I could load/parse into a variable $lang and then when I generate the HTML content I use the proper variable.

  <h1><?php echo $lang['title'];?></h1> <p><?php echo $lang['paragraph1'];?></p>

If you finally go through this solution I recommend you to use some template engine (such smarty or twig). You will have a more much clean code.