[ic] Use one table for product descriptions in different languages

[Ethan Rowe]

Daniel Davenport wrote:

>  
>
>>>>lars.tode at bpanet.de 07/08/05 12:34 PM >>>
>>>>        
>>>>
>>Hallo list.
>>
>>One of our customers thought about translate his shop in different
>>languages. The biggest part of the translation is done by the locale.txt
>>There is just one exception, the title and description of each product.
>>
>>One possible solution is to use different ProductFile for each language,
>>using the ProductFiles directive in the locale.txt
>>In this solution, I'd to create more than one product table and to
>>update both tables if something changed (data, table structure etc).
>>This idea is not bad, but i search for something different.
>>
>>Another solution I found today on www.icdevgroup.org, DescriptionField.
>>I think the solution ist not so bad, except if you add more languages.
>>
>>My idea is just add one more table where every descption for every
>>language and item is stored in.
>>
>>I thought about following table layout :
>>
>>item_code
>>ianguage_id
>>title
>>description
>>
>>    
>>
Are you using a real database engine?  Not text files, Excel files, etc.?

I've used this kind of table.  In my implementation, the main products 
table contains the usual, product-wide information.  From there, a 
products_language table following a format similar to what you have 
above abstracts out language-specific descriptive information about 
products.  Working with this kind of data model is pretty trivial, as 
long as you're comfortable with SQL.

Ideally, you would use the item_code and language_id columns as a 
composite primary key.  Therefore, you need in catalog.cfg:
Database  <your_table> COMPOSITE_KEY item_code language_id
(and don't expect updates in the table editor to behave properly if you 
change values in one/either of the key columns!  The table editor will 
insert a new record based on the new key combination, rather than change 
the key values of the original record.  This can have hazardous side 
effects if you're not careful.)

For my purposes, my products queries always start from the products 
table, naturally, but then LEFT JOIN against the products_language table 
to get the language-specific descriptive information.  In your SELECT 
field list clause, you can use COALESCE(...) to degrade gracefully from 
the language-specific description to a default description.  In my case, 
I wanted to English to be the default, and wanted to avoid redundant 
data, so I removed all descriptive info from the products table 
completely.  Thus,
SELECT
    COALESCE(pl_lang.description, pl_def.description, 'Not Available') 
AS description
FROM products p
LEFT JOIN products_language pl_lang
    ON pl_lang.sku = p.sku
    AND pl_lang.language = 'ja'
LEFT JOIN products_language pl_def
    ON pl_def.sku = p.sku
    AND pl_def.language = 'en'

The above is sort of MySQL-focused; for PostgreSQL use, you could put 
the "pl_lang.language= 'ja'" clause and the "pl_def.language = 'en'" 
clause under WHERE rather than as join conditions; PostgreSQL's 
optimizer will figure it out.  Same issues if you needed 
product-specific results (i.e. p.sku = <some_sku>); for MySQL you'd 
probably want the filter in the join conditions, but for PostgreSQL it 
could go in a WHERE clause.

>>Is there a possibillity the modify/configure Interchange in that way,
>>that it can handle this kind of table?
>>Or is the only way to create an own usertag, which reads the data from
>>the second table?
>>
>>Thanx in advanced,
>>
>>Lars
>>    
>>
>
>You'd have a hard time telling IC to look at both the sku and the language with a scheme like that.
>
>[data table=descriptions field=description key=1
>    foreign.item_code=[scratch sku]
>    foreign.language_id=[scratch current_language]
>]
>
>looks like it might grab the data you need, assuming you've [tmp]ed or [set] your sku and language.
>If you want it simpler than that in a page, you'll probably need to write a tag....but the tag could just be a wrapper around the data tag above.
>  
>
I would think it preferable to just write the query SQL yourself, rather 
than relying on the Interchange data tags.  Not to mention that it is 
frequently more efficient to query a table for all the results you want 
rather than using the [data] tag which generally means a different query 
for every single use.  If the language is set in the session, then 
getting this kind of stuff to work on the product flypage is a 
relatively simple matter; you expand the field list of the query I 
outlined above such that it provides all the data you need, use it in a 
[query list=1 sql=|...|] tag at the start of the flypage (within the 
[item-list]...[item-list] block expected therein), and you're good.

Hoping this is remotely helpful,
- Ethan

-- 
Ethan Rowe
End Point Corporation
ethan at endpoint.com