Hi, I would like to "translate" queries into foreign languages without modifying the application. Can this be done?

Imagine you have a table called "shirts" with descriptive information about shirts written in English. One of the columns is called "color". Your application has the following SELECT statement:

SELECT * FROM shirts WHERE color = "red"

You want to translate this table into Spanish for a different audience, and "red" becomes "rojo". I cannot change the application. I would like to have some kind of DB middle layer which takes my query, translates it into Spanish, and retrieve identical results. In this case, the middle layer would take the original query, translate "red" to "rojo" and retrieve:

SELECT * FROM shirts WHERE color = "rojo"

Can this be done in modern databases? Do I need to use ODBC? I'm new to these concepts so any ideas would be appreciated.

Thank you,
David

I'm going to go out on a limb and guess that you really want to be able to wire the query using multiple languages (one language in the DB, more than one exposed to the user) and have the application translate the user's language to the database language for the purposes of queries. If that is the case, then you can do one of two things...

The best answer is to change the database to make the queries work cleanly using any human language you choose to support, but this means that you have to change the database to support this kind of query. This allows you to have the data in your database stored in natural form, meaning that when the user is using a Chinese or Hindi web page the content is available in correct Chinese or Hindi.

An answer that is usually good enough is to have your middle tier translate from the language being presented to the end user into a single language in the database. This means that the query written in Arabic or Russian gets translated so that it retrieves the correct data, but that content is presented in the database standard langauge (such as English).

You need to think a bit more about how ambitious you want to be, then reformulate your question.

-PatP

I think I understand what you are saying, but I believe the question is as before. There are several users who have the same copy of the application (perhaps they've even modified it slightly). I have no control over them or their application version. But I do have control over the data, copies of which they get.
In other words, it's the data that are becoming multi-lingual. An original table written in English is translated using basic translations into a Spanish table, a French table, etc. I want to be able to ship to these users, along with their version of the table, some kind of code which allows their applications to retrieve the same results as when they had the English table version. What should I ship them? Would it be some ODBC plug-in or some other mediating layer? If it's doable, how does one design such a thing?

Thank you

if you control the data the user sees then you need to supply the data in a language that is appropriate to the user.

ie if you know what words meani in different languages
how you implement that depends entirely on your design.

in a programming environment these days you would distribute your application with a suitbale resource bundle which would include local language versions of text used in the app. you can replicate that approach by storing different translations within, say, you master db. effectively you use an identifier to pull the appropriate text. if the db is only used in a single language environment then you could consider deplying it withthe appropriate language.

ultimately its up to you, what your design is

Thank you for this, but again, I'm not in control of the user app. I am not comfortable changing his local language because I don't know everything he's doing. Further, the assumption is his code is fixed. Therefore, I can't really provide him with multiple tables and via an index issue SELECTs on the table relevant for him. I have to assume he only has one table whose name is hardcoded in the application--I have to work with that name. The other thing I can't do is issue queries against the original table, and translate the result set into the language he's looking for before presenting to the user. I know that the table is being translated for other reasons, mostly due to hardcoding, and the original table will no longer be available after it's updated.

What I'm looking for is a programming mechanism that allows for a query to esentially be re-written. For example, imagine his application consists of only one line:

SELECT * FROM shirts WHERE color = "red"

On the current table, it will return, say, 7 records.

I will ship him a Spanish table where "red" has been translated into "rojo". When his application issues the same SELECT statement, he'll get 0 rows because "rojo" != "red". What I need is a way to intervene in the DBMS parsing mechanism so that when this SELECT statement is prepared, optimized, etc. I change the incoming "red" into "rojo" so that I can return the same results as before.

Can this be done? In my readings about ODBC programming, for example, it appears that the original application has to have handles, SQL statement construction, cursor management, etc. The original application in this case has none of that. I know that it's just a bunch of SELECTs and some other non-complex statements. Can I update the DBMS parser or some other DBMS mechanism, using my own C or Java code, which will intercept the incoming string (e.g. "red") and convert it into the translated string (e.g. "rojo") and return the same results? I'm trying to understand where should this be done within the DBMS.

Let me know if this is doable.

Thank you.

no its not about translating queries, its about translating the underlying data.

depending on what the level of localisation is you could get away with an equivalency

eg
products
id
<blah>

productcolours
id
colourid

languages
id
name

productcolourslanguage
colourid
languageid
colour

productdescriptions
productid
languageid
description

so say you had a shirt
you would have an entry in product for a shirt
you would have an entry in product descriptions which described a shirt in all the various languages you support
you would have a table with all the varous colours that your products need
you would have an entry fo each and every langauage for those colours. (eg Red, Rotte, Roja, Rouge etc....)
by assocaiting a product with a colourid you then have transparency on different languages description of the colour.

however that is going to be a problem for data management and deployment. Unless you need to deploy the same language in the same app, in which case you will have too deploy such intersection tables separately definign language descriptions/text.

the alternative (for a single language per app) is to maintian your central multi language db, but extract the data relevant to that language port of your application.
eg if its English, then only copy accross the English desciption, French the French version, America the Ameriian version and so on

Wow, thank you. I appreciate the persistence. But I cannot create any associations or tables. Really. Here is the stark picture. There exists one table in this app, shirts, with the following columns:


material manufacturer product_id color price size (perhaps some others)


And there are a bunch of rows, signifying all shirts in stock. This table will be destroyed (e.g. DROPed), and a new one will be created with the exact same schema/column names. It will be populated with rows which have translated strings. (Although some strings will not change, e.g. product_id, because they don't need to be translated).

I'm looking for a parser-intervening mechanism to do this, not in any way of restructuring databases. It's clunky but is it possible?

Thank you,

What DBMS are you using? If it's an open source one then maybe you could modify the source code. Getting your customers to agree to install that might be a big problem though. I seriously doubt whether it's worth it. Most likely you would be better off developing a new application from scratch.

What I suggest is that you reconsider whatever is the business driver for this and start with a product strategy rather than focussing on one perceived technical problem.

well what you could do is run a series of update queries which change the, say, English (or American) text and replace with the local language equivalent, for deployment of that table in another language.

eg
update mytable set colour = "Rojas" where colour="Red"
or
update mytable set colour = "Jeune" where colour="yellow"

however you would need tobe very very careful when doing that as you could end up with mangled data.

You would need to proof read ALL the data to make sure it was correct.

i still think the best route is probably to have a master central db with all this data in it, and then distribute the data to the client apps with appropriate local language text.

there is no intermediate methoid that I'm aware of that will accept user request in one language which then "talks" to the db in another language and returns values in the original language the user uses.

ie its not possible to interpret where colour = "Rojas" to extract all garments which are red and retrun them tot he user as "Rojas", without some unser interface... its not possible at SQL level, it is perfectly possible with the user front end. if, ferinstance, the fron end, the user interface, was written in PHP you could do your translation/interpreation there. the front end could maintain its own translation tables for things like colour, product description and so on. it makes the conversion from local language to data lanaguage.

it would be safer than doing a series of SQL updates as you could use the power of SQL to find words/text that you haven't got a description of

What happens when Joe from the US and Juan from Spain are both trying to buy a shirt at the same time?

Thanks, but I'm still curious if some mediation can be done. A number of these users are MS SQL so I will explore that. Thank you.

Thanks, but I'm still curious if some mediation can be done. A number of these users are MS SQL so I will explore that. Thank you.