Re: Newbie: How to deal with multiple languages
thanks for all the help this will help as I think the client wants this project in 3 languages On Nov 28, 2004, at 7:25 AM, Rhino wrote: - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 27, 2004 5:36 AM Subject: Re: Newbie: How to deal with multiple languages Hello. You can find an answer here: http://dev.mysql.com/doc/mysql/en/Charset.html MySQL supports column character sets on columns of some types (char,varchar,text). Probably if I were you I would use Unicode in my application. Graham Anderson [EMAIL PROTECTED] wrote: I have a mysql db that contains tables with multiple language fields for example... Artist_id 'PK' Artist_name Artist_pictLink Artist_purchaseLink Artist_bio_Spanish Artist_bio_English Artist_bio_German I have other tables with a similar layout...Is this needlessly complicated ? track_id 'PK' Artist_id 'FK' track_name_Spanish track_name_English track_name_German track_path track_versionTotal track_purchaseLink track_pictLink Is there a better way to deal with tables that need multiple language fields...like creating another Db for that language ? trying to get the design down before I end up with a huge headache... You *could* alter your design to do something like this: create table artist (artist_id [column type] not null, artist_name [column type] not null, artist_pictlink [column type], artist_purchaseLink [column type], artist_bio_code int, primary key (artist_id) foreign key artist_bio_code references artist_bio on delete restrict) Type=InnoDB; create table artist_bio (artist_bio_code int not null, artist_bio_Spanish [column type], artist_bio_English [column type], artist_bio_German [column type], primary key(artist_bio_code)) Type=InnoDB; You would then have to join to get the artist_bio information in the desired language(s) but, of course, you wouldn't have to do the join unless you needed the bio. The dramatically smaller size of your artist table could help your performance for those queries where you don't need the bio. Naturally, queries that need the bio will have a bit more work to do to get the bio. Both designs lend themselves to supporting additional languages if that should become necessary. I think that is very important because I can easily imagine having to increase the number of languages. I haven't done any work with character sets in MySQL so I don't know if there would be any advantage to having the foreign character data separated into their own tables so that 'main' tables like 'Artist' would have only standard characters. You should probably read the chapter on character sets that Gleb cited to try to figure that out. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] graham anderson 310.402.3980 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: How to deal with multiple languages
Hello. You can find an answer here: http://dev.mysql.com/doc/mysql/en/Charset.html MySQL supports column character sets on columns of some types (char,varchar,text). Probably if I were you I would use Unicode in my application. Graham Anderson [EMAIL PROTECTED] wrote: I have a mysql db that contains tables with multiple language fields for example... Artist_id 'PK' Artist_name Artist_pictLink Artist_purchaseLink Artist_bio_Spanish Artist_bio_English Artist_bio_German I have other tables with a similar layout...Is this needlessly complicated ? track_id 'PK' Artist_id 'FK' track_name_Spanish track_name_English track_name_German track_path track_versionTotal track_purchaseLink track_pictLink Is there a better way to deal with tables that need multiple language fields...like creating another Db for that language ? trying to get the design down before I end up with a huge headache... many thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: How to deal with multiple languages
- Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 27, 2004 5:36 AM Subject: Re: Newbie: How to deal with multiple languages Hello. You can find an answer here: http://dev.mysql.com/doc/mysql/en/Charset.html MySQL supports column character sets on columns of some types (char,varchar,text). Probably if I were you I would use Unicode in my application. Graham Anderson [EMAIL PROTECTED] wrote: I have a mysql db that contains tables with multiple language fields for example... Artist_id 'PK' Artist_name Artist_pictLink Artist_purchaseLink Artist_bio_Spanish Artist_bio_English Artist_bio_German I have other tables with a similar layout...Is this needlessly complicated ? track_id 'PK' Artist_id 'FK' track_name_Spanish track_name_English track_name_German track_path track_versionTotal track_purchaseLink track_pictLink Is there a better way to deal with tables that need multiple language fields...like creating another Db for that language ? trying to get the design down before I end up with a huge headache... You *could* alter your design to do something like this: create table artist (artist_id [column type] not null, artist_name [column type] not null, artist_pictlink [column type], artist_purchaseLink [column type], artist_bio_code int, primary key (artist_id) foreign key artist_bio_code references artist_bio on delete restrict) Type=InnoDB; create table artist_bio (artist_bio_code int not null, artist_bio_Spanish [column type], artist_bio_English [column type], artist_bio_German [column type], primary key(artist_bio_code)) Type=InnoDB; You would then have to join to get the artist_bio information in the desired language(s) but, of course, you wouldn't have to do the join unless you needed the bio. The dramatically smaller size of your artist table could help your performance for those queries where you don't need the bio. Naturally, queries that need the bio will have a bit more work to do to get the bio. Both designs lend themselves to supporting additional languages if that should become necessary. I think that is very important because I can easily imagine having to increase the number of languages. I haven't done any work with character sets in MySQL so I don't know if there would be any advantage to having the foreign character data separated into their own tables so that 'main' tables like 'Artist' would have only standard characters. You should probably read the chapter on character sets that Gleb cited to try to figure that out. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: How to deal with multiple languages
Looks good to me but I'm a relative beginner. Maybe another option - make Spanish, English, and German tables, then link those to the main table. This however can slow you down if you are doing multiple reads of the various tables. Respectfully, Ligaya Turmelle Graham Anderson wrote: I have a mysql db that contains tables with multiple language fields for example... Artist_id 'PK' Artist_name Artist_pictLink Artist_purchaseLink Artist_bio_Spanish Artist_bio_English Artist_bio_German I have other tables with a similar layout...Is this needlessly complicated ? track_id 'PK' Artist_id 'FK' track_name_Spanish track_name_English track_name_German track_path track_versionTotal track_purchaseLink track_pictLink Is there a better way to deal with tables that need multiple language fields...like creating another Db for that language ? trying to get the design down before I end up with a huge headache... many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: How to deal with multiple languages
Personally I would keep a table for the translations. Some identifier for what it is, and then and id for the language. So if something doesn't exist in one language you know about it and can default to another language. It also makes it easier to add new languages in my opinion. somthing like: artist_id | 1234 content | bio language | english artist_id | 1234 content | bio language | french artist_id | 1234 content | bio language | german You can index the 3 columns together. Could also simply be 2 field that makes it a bit more vesatile content and language bio_1234 english bio_1234 french description_1234 spanish Ligaya Turmelle wrote: Looks good to me but I'm a relative beginner. Maybe another option - make Spanish, English, and German tables, then link those to the main table. This however can slow you down if you are doing multiple reads of the various tables. Respectfully, Ligaya Turmelle Graham Anderson wrote: I have a mysql db that contains tables with multiple language fields for example... Artist_id 'PK' Artist_name Artist_pictLink Artist_purchaseLink Artist_bio_Spanish Artist_bio_English Artist_bio_German I have other tables with a similar layout...Is this needlessly complicated ? track_id 'PK' Artist_id 'FK' track_name_Spanish track_name_English track_name_German track_path track_versionTotal track_purchaseLink track_pictLink Is there a better way to deal with tables that need multiple language fields...like creating another Db for that language ? trying to get the design down before I end up with a huge headache... many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: How to deal with multiple languages
I have a mysql db that contains tables with multiple language fields for example... Artist_id 'PK' Artist_name Artist_pictLink Artist_purchaseLink Artist_bio_Spanish Artist_bio_English Artist_bio_German I have other tables with a similar layout...Is this needlessly complicated ? track_id 'PK' Artist_id 'FK' track_name_Spanish track_name_English track_name_German track_path track_versionTotal track_purchaseLink track_pictLink Is there a better way to deal with tables that need multiple language fields...like creating another Db for that language ? trying to get the design down before I end up with a huge headache... many thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
