...specific table for each language would have the following structure: ProdID and Description.
...which is why you're having to look at a really involved setup to get your information out of the database.
A better approach would be to use a single table, and use a code for the language. You don't want to be defining a column per attribute you want translated either, so you'd be looking at implementing something like:
LANGUAGES
table
LANGUAGE_ID
, pk
LANGUAGE_DESCRIPTION
Example data:
LANGUAGE_ID | LANGUAGE_DESCRIPTION
------------------------------------
1 | ENGLISH
2 | FRENCH
TRANSLATED_ATTRIBUTES
table
TRANSLATED_ATTRIBUTE_ID
, pk
TRANSLATED_ATTRIBUTE_DESC
Example data:
TRANSLATED_ATTRIBUTE_ID | TRANSLATED_ATTRIBUTE_DESC
------------------------------------
1 | PROD_ID
2 | PROD_DESC
LOCALIZATIONS
table
LANGUAGE_ID
, pk
TRANSLATED_ATTRIBUTE_ID
, pk
TRANSLATED_VALUE
Example data:
LANGUAGE_ID | TRANSLATED_ATTRIBUTE_ID | TRANSLATED_VALUE
----------------------------------------------------------
1 | 1 | Product ID
2 | 1 | Produit ID
You'll want a table associating the TRANSLATED_ATTRIBUTE_ID
with a given item - Product is the example you've given so:
ATTRIBUTES
table
ATTRIBUTE_ID
, pk
ATTRIBUTE_TYPE_CODE
, fk
TRANSLATED_ATTRIBUTE_ID
, fk
Example data:
ATTRIBUTE_ID | ATTRIBUTE_TYPE_CODE | TRANSLATED_ATTRIBUTE_ID
----------------------------------------------------------------
1 | PRODUCT | 1
If you want to relate on a per product basis:
ATTRIBUTES
table
ATTRIBUTE_ID
, pk
PRODUCT_ID
, fk
TRANSLATED_ATTRIBUTE_ID
, fk
Now can you use two parameters - the language (English) & what the item is (Product):
SELECT t.translated_attribute_desc,
t.translated_value
FROM LOCALIZATIONS t
JOIN TRANSLATED_ATTRIBUTES ta ON ta.translated_attribute_id = t.translated_attribute_id
JOIN ATTRIBUTES a ON a.translated_attribute_id = ta.translated_attribute_id
JOIN ATTRIBUTE_TYPE_CODES atc ON atc.attribute_type_code = a.attribute_type_code
JOIN LANGUAGES lang ON lang.language_id = t.language_id
WHERE lang.language_description = 'ENGLISH' --alternate: lang.language_id = 1
AND atc.attribute_type_code = 'PRODUCT'
You can pivot the data as necessary.