How to design database schema for a content management system (cms) with multiple language support?

Question

I am going to build a site with multiple language support, and I need to have the ability to control the workflow of the articles, companies, products. All with multiple language support and multiple versioning. Does anyone have the solution for this already or I need to start from scratch?

Answer 1

As your question is very broad, I will attempt to give you an answer with a simple example. This can be extended to every entity you want to store. Let us assume you have an article entity.
Article (articleID, langID, ENUTitle, ENUContent, authorID)

By default you can store English language content in the main table. The same content or translated content can be stored in a separate language translation table.

Article_Translation(ID, articleID, langID, langTitle, langContent)

example of content

insert into article values ('art101','ENU','New Website for Developers','Stackoverflow is new and useful','BKM')

Insert into article_translation (1023, 'art101','FRA','nouveau site Web pour les développeurs','stackoverflow est nouveau et utile','BKM')

Insert into article_translation (1024, 'art101','SPA','nuevo sitio web para desarrolladores','Stackoverflow es nuevo y útil','BKM')

Depending on your user language preference, content can either be displayed from article table or article translation table

In general, for every entity that needs translation, you will need a main table and language translation table.