In terms of database structure, is it better to store language-specific content in separate columns or separate tables for a multilingual website?

When dealing with language-specific content for a multilingual website, it is generally better to store the content in separate tables rather than separate columns. This allows for easier management and scalability as new languages can be added without altering the existing database structure. It also helps maintain data integrity and makes it easier to query and retrieve language-specific content. 

// Example of storing language-specific content in separate tables

// Table structure for storing content in different languages
CREATE TABLE `content_en` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `content_fr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// Query to retrieve content in English
SELECT * FROM `content_en`;

// Query to retrieve content in French
SELECT * FROM `content_fr`;

Keywords

database structure multilingual website separate columns separate tables PHP

Related Questions