[ic] Use one table for product descriptions in different languages
ethan at endpoint.com
Mon Jul 11 13:18:07 EDT 2005
Daniel Davenport wrote:
>>>>lars.tode at bpanet.de 07/08/05 12:34 PM >>>
>>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 :
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
COALESCE(pl_lang.description, pl_def.description, 'Not Available')
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,
>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,
End Point Corporation
ethan at endpoint.com
More information about the interchange-users