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]
RE: Requesting help with subquery
Thanks, again, for folks who suggested solutions to my problem. To help users searching the archives, I've pasted in a working solution at the end of this message. Also, I'd like to ask if there is a more efficient or better way of checking if the language version exist than the six lines I repeated 8 times below. Thanks, again. -Kevin =\ I originally wrote: I have a database of publications in different languages. main categories are organized into sub categories with baseitems of publications. Each baseitem can be printed in one or more of eight languages. My SQL query so far is: snip Working solution: [EMAIL PROTECTED]:~$ cat OrderDB-requested.sql SELECT m.title AS Main Category, s.title AS Sub Category, b.partno AS Part Number, (SELECT lv.title FROM langversion AS lv WHERE lv.langid = 1 # English = 1 AND b.baseitemid = lv.baseitemid ) as English Title, CONCAT( IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 1 # 1 = English AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'E', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 2 # 2 = French AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'F', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 3 # 3 = Spanish AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'S', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 4 # 4 = Portuguese AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'P', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 5 # 5 = Arabic AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'A', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 6 # 6 = Swahili AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'W', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 7 # 7 = Russian AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'R', '-'), IF(EXISTS (SELECT * FROM langversion AS lv WHERE lv.langid = 8 # 8 = Turkish AND b.baseitemid = lv.baseitemid AND lv.available = 'Y' ), 'T', '-') )AS Lang Avail FROM maincategory AS m JOIN subcategory AS s ON m.maincatid=s.maincatid JOIN baseitem AS b ON s.subcatid=b.subcatid WHERE b.available = Y ORDER BY m.title, s.title; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Requesting help with subquery
I'm trying to use a subquery for the first time, and am having some problems. I'm hoping someone can give me some suggestions on ways of doing what I want, and further suggestions if there's a better way of going about my task. I have a database of publications in different languages. main categories are organized into sub categories with baseitems of publications. Each baseitem can be printed in one or more of six languages. My SQL query so far is: SELECT m.title AS Main Category, s.title AS Sub Category, b.partno AS Part Number, (SELECT lv.title FROM langversion AS lv JOIN baseitem AS b2 ON lv.baseitemid = b2.baseitemid WHERE lv.langid = 1 # English = 1 AND b.baseitemid=lv.baseitemid ) as English Title, IF(ISNULL(SELECT lv.langversionid FROM langversion AS lv JOIN baseitem AS b3 ON lv.baseitemid = b3.baseitemid WHERE lv.langid = 1 AND b.baseitemid = lv.baseitemid )), 'Y', 'N') AS Lang Avail FROM maincategory AS m JOIN subcategory AS s ON m.maincatid=s.maincatid JOIN baseitem AS b ON s.subcatid=b.subcatid WHERE b.available = Y ORDER BY m.title, s.title; This gives me an error at line 11, IF(ISNULL(SELECT This should give me a Y if the English version exists, and a N otherwise. If I modify it like this, it works: SELECT m.title AS Main Category, s.title AS Sub Category, b.partno AS Part Number, (SELECT lv.title FROM langversion AS lv JOIN baseitem AS b2 ON lv.baseitemid = b2.baseitemid WHERE lv.langid = 1 AND b.baseitemid=lv.baseitemid ) as English Title, (SELECT lv.langversionid FROM langversion AS lv JOIN baseitem AS b3 ON lv.baseitemid = b3.baseitemid WHERE lv.langid = 1 AND b.baseitemid = lv.baseitemid ) AS Lang Avail FROM maincategory AS m JOIN subcategory AS s ON m.maincatid=s.maincatid JOIN baseitem AS b ON s.subcatid=b.subcatid WHERE b.available = Y ORDER BY m.title, s.title; I think this demonstrates that the two subqueries are working. What I'd ultimately like to do is produce a string like YNNYYN where Y is printed if the language version of the baseitem exists (is not null?). I was going to do this by creating a SELECT subquery for each language version possible and CONCAT the Y or N together. Can anyone suggest where I'm going wrong in my attempts? Is there a better way overall to produce this information? Thanks in advance for your help 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]
RE: Requesting help with subquery
Thank you, Johan and Chris, for finding my obvious mistakes. Unfortunately, even after fixing them, I still get an SQL error: [EMAIL PROTECTED]:~$ cat OrderDB-requested.sql SELECT m.title AS Main Category, s.title AS Sub Category, b.partno AS Part Number, (SELECT lv.title FROM langversion AS lv JOIN baseitem AS b2 ON lv.baseitemid = b2.baseitemid WHERE lv.langid = 1 # English = 1 AND b.baseitemid=lv.baseitemid ) as English Title, IF((IFNULL(SELECT lv.langversionid FROM langversion AS lv JOIN baseitem AS b3 ON lv.baseitemid = b3.baseitemid WHERE lv.langid = 1 AND b.baseitemid = lv.baseitemid ), 0), 'Y', 'N') AS Lang Avail FROM maincategory AS m JOIN subcategory AS s ON m.maincatid=s.maincatid JOIN baseitem AS b ON s.subcatid=b.subcatid WHERE b.available = Y ORDER BY m.title, s.title; [EMAIL PROTECTED]:~$ mysql orderDB OrderDB-requested.sql ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT lv.langversionid FROM langversion AS lv JO' at line 11 [EMAIL PROTECTED]:~$ -Kevin -Original Message- From: Chris Sansom [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 12:45 PM To: Zembower, Kevin; mysql@lists.mysql.com Subject: Re: Requesting help with subquery At 11:40 -0400 26/9/06, Zembower, Kevin wrote: IF(ISNULL(SELECT lv.langversionid FROM langversion AS lv JOIN baseitem AS b3 ON lv.baseitemid = b3.baseitemid WHERE lv.langid = 1 AND b.baseitemid = lv.baseitemid )), 'Y', 'N') AS Lang Avail Looks to me as if your parentheses don't balance here - you have an extra ')' in that last line. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I once preached peaceful coexistence with Windows. You may laugh at my expense - I deserve it. -- Jean-Louis Gassé, former Apple executive ( Be CEO) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
