|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Multilanguage supportWonder if there a general way to build a database with support for multilanguage. Say I have a Product table. CREATE TABLE ( Id Title Info Price Quantity ... ) Here I want Title and Info to be stored in x languages. Should I just add columns like: Title_Default, Title_Esp, Title_Ger and so on. Or should the text be stored in a separate table in some way? Any ideas? Hi
Seperate table. Whilst on this, are you sure that you will use the same price for all languages too? Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "Senna" wrote: > Hi > > Wonder if there a general way to build a database with support for > multilanguage. > > Say I have a Product table. > > CREATE TABLE > ( > Id > Title > Info > Price > Quantity > ... > ) > > Here I want Title and Info to be stored in x languages. Should I just add > columns like: Title_Default, Title_Esp, Title_Ger and so on. > Or should the text be stored in a separate table in some way? > > Any ideas? > > Any ideas on how hat table would look like and how the relation would look
between it and the Product table. The idea for the pricing is so far that each product will have its price set by the default language. And then have a currency table that hold the rate(or what the word is) against the default currency. So if the language is switched it would calc something like this. Price * currenyrate. Is that a good setup? Show quote "Mike Epprecht (SQL MVP)" wrote: > Hi > > Seperate table. Whilst on this, are you sure that you will use the same > price for all languages too? > > Regards > -------------------------------- > Mike Epprecht, Microsoft SQL Server MVP > Zurich, Switzerland > > MVP Program: http://www.microsoft.com/mvp > > Blog: http://www.msmvps.com/epprecht/ > > > > "Senna" wrote: > > > Hi > > > > Wonder if there a general way to build a database with support for > > multilanguage. > > > > Say I have a Product table. > > > > CREATE TABLE > > ( > > Id > > Title > > Info > > Price > > Quantity > > ... > > ) > > > > Here I want Title and Info to be stored in x languages. Should I just add > > columns like: Title_Default, Title_Esp, Title_Ger and so on. > > Or should the text be stored in a separate table in some way? > > > > Any ideas? > > > > Don't confuse languages, currencies and countries. They are all quite
different entities and there isn't necessarily a one to one relationship between them. Each of those belongs in a different table. -- David Portas SQL Server MVP -- I would go for a separate table:
CREATE TABLE product_names (sku_code INTEGER NOT NULL REFERENCES products (sku_code), language_code /* ISO 639 language code */ CHAR(3) REFERENCES languages (language_code), PRIMARY KEY (sku_code, language_code), product_desc VARCHAR(40), UNIQUE (product_desc, language_code /* Ensure that the product description is unique in each language */)) -- David Portas SQL Server MVP -- Just clearify. "sku_code" is?
Show quote "David Portas" wrote: > I would go for a separate table: > > CREATE TABLE product_names (sku_code INTEGER NOT NULL REFERENCES > products (sku_code), language_code /* ISO 639 language code */ CHAR(3) > REFERENCES languages (language_code), PRIMARY KEY (sku_code, > language_code), product_desc VARCHAR(40), UNIQUE (product_desc, > language_code /* Ensure that the product description is unique in each > language */)) > > -- > David Portas > SQL Server MVP > -- > > SKU means Stock Keeping Unit - a code used to identify your products.
In my example I'm assuming SKU_CODE is the primary key of the products table. Your original example had a column called ID but that's a poor name IMO. Each column ought to have a name that describes that attribute uniquely in your schema. "ID" means nothing. It could belong to any table. -- David Portas SQL Server MVP -- Will change my faults. :)
Show quote "David Portas" wrote: > SKU means Stock Keeping Unit - a code used to identify your products. > In my example I'm assuming SKU_CODE is the primary key of the products > table. Your original example had a column called ID but that's a poor > name IMO. Each column ought to have a name that describes that > attribute uniquely in your schema. "ID" means nothing. It could belong > to any table. > > -- > David Portas > SQL Server MVP > -- > > Ok, will add a level to it.
Its obvious not only the Product table that should support this. There are Campaign, Contest, NewsLetter tables and many more. Should each table have its own seperate table or could it work to have one table that every body worked against. In my head it looks something like this. CREATE TABLE texts ( Id, Guid, Language Text ) Product ------------ 2 | d7g287dg-AGUID-asjdhljash | Contest ------------------- 3 | asdhhads-ANOTHERGUID-jashdljd | Is this a good solution or are seperate tables to prefer or are there a even better way? Show quote "Senna" wrote: > Hi > > Wonder if there a general way to build a database with support for > multilanguage. > > Say I have a Product table. > > CREATE TABLE > ( > Id > Title > Info > Price > Quantity > ... > ) > > Here I want Title and Info to be stored in x languages. Should I just add > columns like: Title_Default, Title_Esp, Title_Ger and so on. > Or should the text be stored in a separate table in some way? > > Any ideas? > > I would opt for separate language tables for EACH entity. It's much
easier to manage and maintain data integrity that way. An exception might be if you need to create a separate content management subsystem - maybe to support large and complex localization that requires a dedicated group of people and a distinct process from that of updating the base data. In that scenario it may make sense to create a global "language strings" table but I would certainly try to avoid it otherwise. -- David Portas SQL Server MVP -- Ok, I'll go with seperate tables.
Thanks everybody for your answers. Show quote "David Portas" wrote: > I would opt for separate language tables for EACH entity. It's much > easier to manage and maintain data integrity that way. An exception > might be if you need to create a separate content management subsystem > - maybe to support large and complex localization that requires a > dedicated group of people and a distinct process from that of updating > the base data. In that scenario it may make sense to create a global > "language strings" table but I would certainly try to avoid it > otherwise. > > -- > David Portas > SQL Server MVP > -- > > Products table:
productId Price Quantity productLanguages language productID title info This makes it easier to add new languages then the method you suggest where you would have to add new columns when adding a language. This also simplifies querying a product depending on the language. In the product table the key is productID. In the productLanguage table the key is language/productID. There are other ways to implement this, both in the database and client side. Maybe someone here can suggest other methods. Show quote "Senna" <Se***@discussions.microsoft.com> wrote in message news:2648721B-5369-45E6-85E4-8CF3AAD84B70@microsoft.com... > Hi > > Wonder if there a general way to build a database with support for > multilanguage. > > Say I have a Product table. > > CREATE TABLE > ( > Id > Title > Info > Price > Quantity > ... > ) > > Here I want Title and Info to be stored in x languages. Should I just add > columns like: Title_Default, Title_Esp, Title_Ger and so on. > Or should the text be stored in a separate table in some way? > > Any ideas? > >  |
|||||||||||||||||||||||
Other interesting topics