List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:October 10 2006 2:12pm
Subject:RE: Advice on multilingual databases?
View as plain text  
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:kzembowe@stripped]
> Sent: Tuesday, October 10, 2006 9:36 AM
> To: mysql@stripped
> 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/mysql?unsub=1
>
>



Thread
Advice on multilingual databases?Kevin Zembower10 Oct
  • RE: Advice on multilingual databases?Jerry Schwartz10 Oct