multilingual CMS - how to design the database

[zoreli]

Hi

I have the following question. I have my bunch of libraries - modules (I can't name that a CMS, even it is look like one, since customer can edit almost all parts of his website), which I use in my everyday projects which are - customer websites.

I have the following modules: products, news, faq, services, projects and articles (which are actually pages like contact, about us, our history, facilities, privacy policy... you name it)

I live and work in Greece, hence standard webiste here is considered 2 languages, Greek and English. It happens however customer to request 3,4 or more languages. My libraries - modules and database tables are created for 2 languages, and if customer request more than 2 languages, I have to alter all the modules that have to be installed on the customer website.

For example, here is how FAQ Categories table look like:

CREATE TABLE IF NOT EXISTS `faq_categories` (
  `catid` int(11) NOT NULL AUTO_INCREMENT,
  `parentid` int(11) DEFAULT NULL,
  `categoryname` varchar(255) NOT NULL,
  `categoryname_en` varchar(255) DEFAULT NULL,
  `description` text,
  `description_en` text,
  `metatags` text,
  `metatags_en` text,
  `sorder` int(11) NOT NULL,
  `visible` tinyint(4) NOT NULL,
  `categoryphoto` varchar(255) DEFAULT '',
  PRIMARY KEY (`catid`),
  KEY `parentid_fk` (`parentid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=195 ;

Now, as you can imagine, if customer request one more language, I have to alter not only the database tables, but the php code as well, which on large project may be quite cumbersome.

Can anyone propose better database design than this? It would be nice to have somewhere langid field, and the record to be retrieved on base on that, but I have no clear picture of how that may work. Any idea or link for further reading will be deeply appreciated.


I post this question here on database forum because If I create the design of the database right, I will find my way in CodeIgniter (php).



Regards,Zoran

[r937]

Can anyone propose better database design than this?

continue to use the existing structure for table relationships, but split off the language-sensitive columns into a one-to-many table...

CREATE TABLE faq_categories 
( catid         INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
, parentid      INTEGER NULL
, sorder        INTEGER NOT NULL
, visible       TINYINT NOT NULL
, categoryphoto VARCHAR(255) 
, KEY parentid_fk (parentid)
);
CREATE TABLE faq_categories_langs
( catid         INTEGER NOT NULL 
, lang          CHAR(2) NOT NULL
, PRIMARY KEY ( catid , lang )
, categoryname  VARCHAR(255) NOT NULL
, description   TEXT
, metatags      TEXT
);

thus each faq_categories row will have multiple faq_categories_langs rows related to it, one for each language

but the main structural attributes (parentid, sort order, etc.) stay with the faq_categories table

[Lemon Juice]

The method proposed by r937 is the more 'proper' one where your data is normalized but in practice I find it easier to work with your original model where you have separate columns for each language - however, I'd end them all with language suffixes (_gr, _en) for better consistency when accessing them. The advantages are less complex queries (no need for joins), easier database viewing, faster queries (if that matters).

The disadvantage is more difficulty adding more languages, so if you want to leave room for expansion then use separate tables from the start. Another disadvantage might be you will need to put more logic in your client language (e.g. php) to query generation like adding language suffixes to column names - but depending on your setup this might not be a problem.

Whenever I am 100% sure a web site will never require more than 2 languages I go with single tables/multiple columns. I manage a fairly complex bilingual online shop with single-table approach and I find it convenient not to have all those separate language tables since there can be many of them in a large database - this results in faster management of the whole system.

[zoreli]

Hi

Thank to both of you for your answers. I appreciate when someone spent his time to help me to solve my problem.

I will have difficult decision to make i guess. I was hoping for something better but...buh.

Anyone know how joomla has solved this problem? Any other open source idea that may be usefull?

Regards, Zoreli

[Lemon Juice]

When you search for "multilingual database design" you will find plently of ideas and also other approaches (but I think it's enough if you choose from the two presented in this thread). Each one has its weaknesses and strengths so it's not a simple yes/no decision. I don't know about joomla but I would expect any ready-made content management system to use one of the normalized solutions like the one presented by r937.