SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2000
    Location
    Bangkok,Thailand
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi all,
    I am involved in a new project that requires that the information is innitially made available in two languages (English and Thai)

    I'm looking for suggesting and advice on how to implement this - If I use the actor details part as an example, I would have the following:

    Table name: Actor_English
    Columns:
    aID
    Name
    Movie
    etc

    and Table Name: Actor_Thai
    Columns:
    aID
    Name
    Movie
    etc

    and Unique ID Table: Actor_ID
    Columns:
    ID
    Language_Flag
    aID

    The boss wants people to be able to search in Thai for example and be given the results in English and visa versa

    So, using the above example, if a user searched for Brad Pitt (typed in English) and wanted the results in Thai, the query would be along the lines of:

    Search through the actor_englaih table, find a match, then go to the ID table, find the ID that corresponds to the aID for Brad Pitt, then find the corresponding aID to the same ID, but that has the Thai language flag, then take that related aID back to the Actor_thai table and pull the details as requested.

    To me, this seems like the "scenic route" but I can't see a better way to do it.

    Any suggestions much appreciated

    Stuart

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    First, there is no need (as far as I can tell) for two different sets of ID's (ID and aID). Consider the following:

    Table: Actor_English
    Columns:
    ID
    Name
    Movie
    etc

    Table: Actor_Thai
    Columns:
    ID
    Name
    Movie
    etc

    Table: Actor_ID
    Columns:
    ID

    This makes your life a lot simpler. To do the example search you mention, join the three tables together by matching the ID columns. In the resulting table, search for the actor's name (Brad Pitt) in the column corresponding to the language specified (English), and retrieve the entire matching entry, including the actor's Thai name.

    The SQL for this would look like the following:

    Code:
    SELECT ID, E.Name, E.Movie, ..., T.Name, T.Movie, ...
    FROM Actor_ID AS A
    LEFT JOIN Actor_English AS E ON A.ID=E.ID
    LEFT JOIN Actor_Thai AS T ON A.ID=E.ID
    Note that this is a pretty advanced query, as it uses two LEFT JOIN's to ensure that entries will be included even for actors that don't have database entries for both languages. If you're not familiar with LEFT JOIN's, read Part 9 of my article series on PHP/MySQL for a complete discussion of their use.

    Of course, the query would be much simpler if you just put all the data in a single table. Is there any particular reason you need to keep them separate?

    I hope this helps, and let me know if you have any further questions!
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2000
    Location
    Bangkok,Thailand
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Kevin,
    Thanks for the suggestions, the main reason we want to keep it in different tables is because later when other languages are added (Bahasa, Korean and Mandarin are all on the cards) it seems more workable if they are all seperate.

    The reason we thought that two sets of ID (ID and aID) would be required would be that if we needed to serve the same information in different languages - say for example, an actor profile in Thai and English then this would be a straightforward way of marrying up the data.

    Thanx

    Stuart


  4. #4
    Don't get too close, I bite! Nicky's Avatar
    Join Date
    Jul 1999
    Location
    Lancashire, UK
    Posts
    8,277
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, you definitely have to think about the future of your database and make sure it is modular. The best idea is to do what you seem to be soign and design it all on paper first and make sure that you normalise all your data.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •