locked
International - multiple language database design. RRS feed

  • Question

  •  

    Could anyone provide me an example of the effective way of design a database support multiple languages. Currently I working to design a database for travel website which support 3 different languages, and more languages will be added in in the future. One of my friend advice me to put different languages into the same table for example

    Table Hotel

    ID

    Description_EN

    Description_ES

    Description_FR

    Location_EN

    Location_ES

    Location_FR

    ....

    But I don't thing it's a good idea to do so since in the future if I would like to add more languages I have to  modify the table and I have to replace all of the sql statements. I am seeking for the best solution , could anyone help.

    Thank you very much

    Tuesday, March 11, 2008 3:08 AM

Answers

  •  

    I think the logic can deal with the condition. E.g. hotel Golden may sites both in France and US. In US, we use $ as price unit (say 90) while in France we may use euro (say 50). We can add two new attributes ‘PriceInDollar’ and ‘Prix’ (Franch) to attribute table, then we mapping it to the language and hotel by table HotelAttributeLanguage. When we query, just as below:

    Join

    (

    Where a.AttributeName=’Prix’ and a.AttributeVaule<=’50’

    )

    It is what I want provided, hope it some helpful, and I think you will have a better solution in future.

    Wednesday, March 12, 2008 11:15 AM

All replies

  • How about:

     

    Code Snippet

    CREATE TABLE Hotel (HotelID int IDENTITY PRIMARY KEY NOT NULL)

     

    CREATE TABLE Language (LanguageID int IDENTITY PRIMARY KEY NOT NULL,

    LanguageDescription varchar(50) NOT NULL)

     

     

    CREATE TABLE HotelDesc (HotelID int NOT NULL FOREIGN KEY REFERENCES Hotel(HotelID),

    LanguageID int NOT NULL FOREIGN KEY REFERENCES Language(LanguageID),

    Description varchar(100) NOT NULL)

     

    CREATE TABLE HotelLoc (HotelID int NOT NULL FOREIGN KEY REFERENCES Hotel(HotelID),

    LanguageID int NOT NULL FOREIGN KEY REFERENCES Language(LanguageID),

    LocationDesc varchar(100) NOT NULL)

     

    You would add a row into HotelDesc or HotelLoc for each language...and add a new row to Language every time you need to add a language.
    Tuesday, March 11, 2008 3:41 AM
  • First I thought the same way as you however I found out there is one problem with this model. If I have multiple attributes in table which required multiple languages ,for instance map location, street address, waterview, etc. I do need to create satellite tables that reference to the Hotel table ,e.g WaterView table, Street table, and so on. That will make the database bigger and sql statement more complex. Do you have another way to do so?

    Thank you.

    Tuesday, March 11, 2008 4:03 AM
  • Ehhh....not really.  The only other model I've seen is to store all translations in one table, but the datatype ends up being unnecessarily large to handle the varying data sizes, and the queries are even uglier.  How many fields from each table are going to contain multilingual data?

     

    Tuesday, March 11, 2008 4:55 AM
  • Well, there are more then 10 fields from the current table that contain multilingual data, and 2-3 fields are dynamic e.g.

    Hotel

    ID

     

    Properties

    ID

    propertyName

     

    Hotel_Properties

    ID

    HotelID

    PropertyID

     

     

    Tuesday, March 11, 2008 8:04 AM
  • How about consolidating the multilingual attributes for a given table into a singular, secondary table?

     

    ex.

     

    Code Snippet

    CREATE TABLE Hotel (HotelID int IDENTITY PRIMARY KEY NOT NULL)

     

    CREATE TABLE Language (LanguageID int IDENTITY PRIMARY KEY NOT NULL,

    LanguageDescription varchar(50) NOT NULL)

     

     

    CREATE TABLE HotelAttrib (HotelID int NOT NULL FOREIGN KEY REFERENCES Hotel(HotelID),

    LanguageID int NOT NULL FOREIGN KEY REFERENCES Language(LanguageID),

    HotelDesc varchar(100) NOT NULL,

    LocationDesc varchar(100) NOT NULL

    --etc.)

     

     

     

    You would have an Attrib table for each translated entity - PropertiesAttrib, etc.
    Tuesday, March 11, 2008 1:19 PM
  • hope it will be some helpful:

     

    Code Snippet

    create table Hotel(HotelID int)
    go
    create table
    Language(LanguageID int, LanguageName nvarchar(50))
    --for multi-language,it is better to use nvarchar data type.
    go
    create table
    Attribute(AttributeID int,AttributeName nvarchar(50),AttributeValue nvarchar(50))
    go
    create table
    HotelAttributeLanguage(HotelID int,LanguageID int,AttributeID int)
    --you should add the foreignkey constraint here for each table

     

     

    Tuesday, March 11, 2008 4:17 PM
  •  

    Thank you very much for your inputs , the solution sound great however I am suffer from the other problem that is how can I perform a search query based on predefined attributes for multiple language . For example, if i would like to search for hotel located in New York, 5 *, has pool, gym, and less than 100 $ per day. Or hotel located in France , 3* has pool, gym, less than 90$ per day( l'hôtel situé dans la France, 3* a la piscine, gymnastique, moins que 90$ par jour in french).

    My head shut down at the moment and couldn't think the solution or I am thinking too much for the perfect solution and just make thing complicate


    Again I need your help , thank you.

    Tuesday, March 11, 2008 10:53 PM
  • I would have all of your logic working in one language (ex. US English) and have the display only in the other languages (ex. just the captions are in French, Spanish, etc....you still query based upon English values).

     

    Wednesday, March 12, 2008 3:03 AM
  •  

    Can this meet your requests?

    select distinct H.HotelID  from Hotel H

    left join HotelAttributeLanguage HAL on H.HotelID =HAL.HotelID

    left join Attribute A on A.AttributeID =HAL.AttributeID

    left join Language L on L.LanguageID =HAL.LanguageID

    where a.AttributeName ='Location' and a.AttributeValue ='New York'

    and a.AttributeName ='Price' and a.AttributeValue <='100'

    and a.AttributeName ='HasGym' and a.AttributeValue ='true'

    Wednesday, March 12, 2008 3:26 AM
  • That query will return 0 rows.  It's not possible for AttributeName to equal Location, Price, and HasGym all at the same time.  Same goes for AttributeValue.  The fundamental problem with attributes as rows is that the query will be necessarily very complex in order to check multiple conditions or fetch multiple values.  You'll essentially have to UNPIVOT the data, which is normally reserved for dealing with tables with rows which should have been written as columns. 

     

    Wednesday, March 12, 2008 4:43 AM
  • What a stupid mistake I have madeL

    Following scripts will fix this issue.

    Code Snippet

    select A.* from

    (

    select distinct H.*  from Hotel H

    join HotelAttributeLanguage HAL on H.HotelID =HAL.HotelID

    join Attribute A on A.AttributeID =HAL.AttributeID

    join Language L on L.LanguageID =HAL.LanguageID

    where  a.AttributeName ='Location' and a.AttributeValue ='New York'

    ) as A

    join

    (

    select distinct H.*  from Hotel H

    join HotelAttributeLanguage HAL on H.HotelID =HAL.HotelID

    join Attribute A on A.AttributeID =HAL.AttributeID

    join Language L on L.LanguageID =HAL.LanguageID

    where a.AttributeName ='Price' and a.AttributeValue <='100'

    ) as B on A.HotelID=B.HotelID

    join

    (

    select distinct H.*  from Hotel H

    join HotelAttributeLanguage HAL on H.HotelID =HAL.HotelID

    join Attribute A on A.AttributeID =HAL.AttributeID

    join Language L on L.LanguageID =HAL.LanguageID

    where  a.AttributeName ='HasGym' and a.AttributeValue ='true'

    ) as C on A.HotelID=C.HotelID

     

     

    But it is a good idea to write a UDF for it (not details but a summary logic):

    Code Snippet

    create function dbo.hotels(@Query xml)

    --not completed, only some logic here

    returns table

    as

    begin

          /*you can add some code here to get the AttributeName and AttributeValue from XML parameter

     

          and insert into a table like #t(Seq, AttributeName, AttributeValue)

          column 'Seq' is with asc order and unique,like 1,2,3...

          */

     

     declare

          @seq        int,

          @sql        nvarchar(max),

          @AttName    nvarchar(50),

          @AttValue   nvarchar(50)

     set @sql='select A.* into MyResult from

                (

                select distinct H.*  from Hotel H

                join HotelAttributeLanguage HAL on H.HotelID =HAL.HotelID

                join Attribute A on A.AttributeID =HAL.AttributeID

                join Language L on L.LanguageID =HAL.LanguageID

                ) as A join '

     select @seq=min(Seq) from #t

     while @seq is not null

     begin

          select @AttName=AttributeName,@AttValue=AttributeValue from #t where @seq=Seq--get attribute info

          set @sql = @sql+'       (

                select distinct H.*  from Hotel H

                join HotelAttributeLanguage HAL on H.HotelID =HAL.HotelID

                join Attribute A on A.AttributeID =HAL.AttributeID

                join Language L on L.LanguageID =HAL.LanguageID

                where  a.AttributeName ='''+@AttName+''' and a.AttributeValue ='''+@AttValue+'''

                ) as A'+convert(varchar(10),@seq) +' on A.HotelID=A'+convert(varchar(10),@seq) +'.HotelID join'

         

          delete from #t where @seq=Seq

          set @seq=null

          select @seq=min(Seq) from #t

     end

     set @sql=left(@sql,len(@sql)-4)-- remove the last 'join'

     exec @sql

     select * into #temp from MyResult

     drop table MyResult

     return(select * from #temp)

    end

     

     

     

     

     

     

    Wednesday, March 12, 2008 6:23 AM
  • I wouldn't ask if all my logic working in one language, the problem is that in some scenarios English attributes are differ with French attributes, for example, hotel name Golden Hotel in English and Hôtel d'or in French, or the hotel price $90 US or 50 euro. How can I write search sql statement ?

    Wednesday, March 12, 2008 9:35 AM
  •  

    I think the logic can deal with the condition. E.g. hotel Golden may sites both in France and US. In US, we use $ as price unit (say 90) while in France we may use euro (say 50). We can add two new attributes ‘PriceInDollar’ and ‘Prix’ (Franch) to attribute table, then we mapping it to the language and hotel by table HotelAttributeLanguage. When we query, just as below:

    Join

    (

    Where a.AttributeName=’Prix’ and a.AttributeVaule<=’50’

    )

    It is what I want provided, hope it some helpful, and I think you will have a better solution in future.

    Wednesday, March 12, 2008 11:15 AM
  •  

    How about spltting every table which might have lanugaage dependent data in to two tables one table handles language neutral data and the other table holds language dependent data.

     

    For example Hotel table

     

    DECLARE TABLE Hotel

    (

    HotelID INT NOT NULL PRIMARY KEY

    DefaultName NVARCHAR(50) NOT NULL

    )

    DECLARE TABLE HotelEx

    (

    HotelID INT NOT NULL PRIMARY KEY

    LanguageName CHAR(5) PRIMARY KEY

    .....

    OTHER Hotel Attribute

    )

     

    Other variation could be 'Hotel' contains all attributes in default langugae and HotelEx contains only language dependent attributes

     

    Wednesday, March 12, 2008 6:45 PM
  • I didn't mean it was stupid!  Your idea is great...just the one query wasn't going to work. 

     

    Wednesday, March 12, 2008 9:00 PM
  •  

    I dont see any thing stupid in this design. I just gave the design hint that will increase the breadth of the database i.e. more tables. Thus more joins in the queries. But the flexibility that is required will be achieved through this.

     

    Thursday, March 13, 2008 1:13 AM