Bilingual database

[Dr Gonzo]

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

[Kevin Yank]

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:

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!

[Dr Gonzo]

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

[Nicky]

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.