Jump to content

Multilingual CMS - Database schema


Lykos22

Recommended Posts

Hi, I'm building a custom cms, in which I'd like to add multilingual functionality. I'm trying to design my database structure at the moment, did a little google research and found two database models, so I was hopping if you could recommend which is the best way to follow.

 

Lets take as an example the Posts table, but same thing applies and with CMS-Pages, News, FAQ etc etc

 

1st scenario:

table Posts (will have all items that will not require translations)---------------------------------------------------------------------post_id (pk)date_addeddate_publishedis_visibletrashetc etctable Posts_lang (will have all items that require translations)-----------------------------------------------------------------id (pk)lang_idpost_idtitlepost_bodytable Languages (the available languages)------------------------------------------------------------------lang_idlanguagelang_code

So in order to fetch the data from the database, I will have to JOIN, like:

SELECT `posts`.`post_id`, `posts`.`date_added`, `posts`.`date_published`, `posts`.`is_visible`, `posts_lang`.`title`, `posts_lang`.`post_body`FROM `posts` LEFT JOIN  `posts_lang` ON `posts`.`post_id` = `posts_lang`.`post_id`WHERE `posts_lang`.`lang_id` = 1 // or use the lang_code instead: // WHERE `posts_lang`.`lang_code` = `eng`

This looks quite easy, but I have some blank spots that I'd hope to make them clear.

1. how would the INSERT or the UPDATE be? Do I have to store the post on default language first and then do the translations, or do all at once ?

 

2. What if there's a cms-page or post that don't have all translated versions, or they have only the default versions? For example a post might have only one language (the default) or 2 / 3 (assuming we have 3 languages available, this post has only 2 languages)??

 

2nd scenario:

table: Posts------------------------post_id (pk)date_addeddate_publishedis_visibletrashtitlecontentetc etctable: Post_translations---------------------------------------post_trans_idpost_trans_titlepost_trans_contentlang_idtable Languages (the available languages)------------------------------------------------------------------lang_idlanguagelang_code

So what you can basicly do, is to store default language content inside posts table, as you would normally do in a no-multilingual scenario, and all translated contents store them inside the posts_translations.

However I'm not quite sure how the queries are going to be in order to fetch the data.

 

Which of these two scenarios do you think its better to follow? Also is there a better way you believe I should follow? I'd be happy to hear!

Edited by Lykos22
Link to comment
Share on other sites

Do I have to store the post on default language first and then do the translations, or do all at once ?

I don't think you necessarily have to do it any way, you can do it whichever way you want. One way would be to have a form that contains a set of fields for each language, so when you're creating the post you fill out each language and submit the whole thing.

2. What if there's a cms-page or post that don't have all translated versions, or they have only the default versions? For example a post might have only one language (the default) or 2 / 3 (assuming we have 3 languages available, this post has only 2 languages)??

Your code that displays posts needs to account for that. If the selected language doesn't exist then show the default language.

Which of these two scenarios do you think its better to follow?

They're pretty similar. The second one would let you get the default and selected languages in the same row easier.
  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...