How can one improve the database design for storing multilingual content in PHP applications?

When storing multilingual content in PHP applications, it is important to design the database in a way that can accommodate multiple languages efficiently. One way to improve the database design is to create a separate table for translations, linking each translation to a unique identifier for the content. This allows for easy retrieval of the correct translation based on the user's language preference. 

CREATE TABLE `content` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `content_key` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE `translations` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `content_id` INT NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    `translation` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`content_id`) REFERENCES `content`(`id`)
);

Keywords

multilingual content database design PHP applications multilingual support localization

Related Questions