Here is my suggestion, but like every other thing I post here I urge you to
take it with a grain of salt. Set up the following tables (described in
rough terms, not in SQL):
topic_index
topic_id autonumber
... Whatever else you need to keep track of that identifies a topic
topic_detail
topic_id long integer primary index
topic_language indexed (perhaps)
parent_topic_id indexed
topic_language_text (utf-8, collate utf8_unicode_ci)
... Whatever else you need that is specific to this language / topic
combo
Now, whenever you add a topic you make an entry for a new topic, you make a
record in topic_index that identifies the topic uniquely. Make corresponding
entries in topic_detail using the topic_id that was just assigned to
topic_index's topic_id: there will be one such record for each language.
Now you can locate the topic itself in the topic_index table and quickly
pull all of the corresponding records for the different languages from
topic_detail. You can also easily find all topics that have entries in a
particular language, or that do not have entries in a particular language,
etc.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -----Original Message-----
> From: Zembower, Kevin [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 10, 2006 9:36 AM
> To: mysql@lists.mysql.com
> Subject: Advice on multilingual databases?
>
> I'd like some advice on setting up databases that contain entries for
> the same item in more than one language. For instance, here's what I
> currently do for a table that contains the same topics translated into
> English and Arabic:
> CREATE TABLE `TOPIC` (
> `TopicID` int(11) NOT NULL auto_increment,
> `Topic-en` text NOT NULL,
> `Topic-ar` text character set utf8 collate utf8_unicode_ci NOT NULL,
> `ParentTopicID` int(11) NOT NULL default '0',
> PRIMARY KEY (`TopicID`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Topics for text-based
> hierarchies' AUTO_INCREMENT=76 ;
>
> In this table, 'Topic-ar' is the Arabic translation of the English
> 'Topic-en.' If this were required to also be in Spanish and
> French, I'd
> add columns for 'Topic-es' and 'Topic-fr' similar to 'Topic-en' above.
>
> I'm wondering if there are any other ways to store and access
> multilingual data. Can anyone suggest other ways they've
> dealt with this
> task, and the pros and cons of their approach compared to mine?
>
> Thank you in advance for your advice and suggestions.
>
> -Kevin
>
> Kevin Zembower
> Internet Services Group manager
> Center for Communication Programs
> Bloomberg School of Public Health
> Johns Hopkins University
> 111 Market Place, Suite 310
> Baltimore, Maryland 21202
> 410-659-6139
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
