multilingual CMS - how to design the database
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 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:
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.
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,
`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 ;
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).