products catalog and shopping cart in 4 languages

[searain]

i have to develop a e-commerce site (product catalog and shopping cart for 4 languages.

i have follow options:

1) 4 times the table fields: each set of the fields for one language, such as fields in table products will be name_english, name_french, price_english, price_french.

depends on the lanaguage user pick, i will display different fields values.

2) 4 times the tables: each set of the tables for one language, such as i will have tables, products_english, products_french.

depends on the language user pick, i will call different tables.

3) 4 times the database: mirror copy the database 4 times, each database for one language. database_english, database_french.

this one is the simplest. i don't have to check the languages and decide which fields i am going to use (option1) or which tables i am going to call (option 2) all over the web sites.

all i need is one databate connection file, with 4 switch case statments, to be included, each language links to its own database.

4) of course, the consistence among 4 languages records for the same item will be enforced by admin tools (in php).

any suggestions? how do you handle multi languages specailly more than 3 languages database back end web sites?

thanks.

[james_holden]

Multi-lingual always seems like a pain but it shouldn't be.

If you are agreed that the fields in your database are going to be used regardless of the language (price, description etc) then you should keep only one database and one table.

In the table you should add ONE new field labelled "language".

This field will be set when the product is created and identify which language it uses.

When you come to select products in a specific lang use:

'select * from product_sheet where x like x and j=j and language like 'french'

All the products will appear that are in french - they will use the same database and table.

---

As an addition if you wanted to ensure that the data is not repetetive - such as quantity - then you can create a simplistic product table with such things as quantity, id etc and anything which falls under the "content for displaying" should be carried to a new table. This way you may be able to alter a product specification, have a global change accross languages and only require a single change to a record. This is most likely the optimum option for data integrity and accuracy which is highly important in ecommerce applications.

Im available at [e-mail] if you need more help.

[searain]

thanks, your suggestion is clean and simple, better than all my original opinions. also it is general design. it doesn't matter which languages are they and how many languages are they.

glad i asked before i go with my original thoughts.

so all i have to do is add one "language" field for all my tables in my one language e-commerce database.

and add one check out, check the language (it could be set, reset in a cookie) for all my sql statments in my web site.

you have done the multi-languages databases/web sites, any other suggestions regarding anything about multi-languages sites?

searain

[james_holden]

Hi,

Heres a quick example:

Id	Pid	ProductName	Description	Language
1	1	A product	Desc		English
2	1	Ein Product	Desc		German
3	1	Un Product	Desc		French
4	1	Gib prod	Desc		Gibberish

All products listed are in actual fact the same thing, they are just different because of the language, therefore we carry the important information accross to a new table which allows us to maintain and manage
critical data away from content based information. For example, Apart from Gibberish and England the other two will use the same currecy (Euros) - so we can start to create a table which allows changes to the
product and they immediatly have a site wide effect without the need to change each record in german and french:

Id	UKPounds	Euro	Dollars	QuantityInStock	NextDelivery	AcceptedCards
1	3.00		5.00	6.00	78				09/11/2002		VISA,MASTERCARD

In this example when ever you buy an item (regardless of language) you affect a central quantity counter and a central price counter. You have no repetitve data and your system cannot allow more purchases than stock available as an example.

What else have I learnt?

Keep it simple - the more complex you make multilingual systems the harder it gets. Keep your content simple and keep information which is not related to the language as far away from the translations as possible.

Unless you are multilingual yourself expect some of the translations to be useless. Make sure you alter the page title and meta data within your different language systems - this increases the chance that foriegn search engines will index your pages. Often we forget that whilst english people use google.com lots of others will use google.fr etc etc

[james_holden]

Additionally you might like to look at the apache <files> directive.

This enables you to use what looks like a directory as a script and as such you can have different websites running without the cookie business:

imagine:

/shop/de/

/shop/fr/

/shop/en/

shop is the script, the /de/ is a variables passed to the shop script, its just made to look like a directory. It allows excellent search engine indexing and means your multi lingual sites are available without cookies.

The whole of http://www.londontown.com/London/Autumn_in_London is powered the same way. The /London/ is a script

[searain]

not the tech question, but the business ones?

one 4 languages (database back end) web site, will cost us more design and programming hours than 4 one language "same kind" (database back end) web site.

right?

The contents/pages are the same, but for the 4 languages site, there is translation issues involved and there is records consistence (such as the price etc.) issue involved.

if that is true, in your experience, about how many times of programming hours will it be for a 4 languages site comparing with the same kind but one language site?

thanks.

searain

[james_holden]

If you develop with multi lingualness in mind (templating and such) then once you have implemented one language you can implement others quickly and simply. Its more related to data entry than programming.

If your market it throughout different countries then you should do it if possible.

Dont design a single language system with the intention of doing a multi lingual one later - you will double your work.