need help/ideas with multi-language site

[spartan]

OK, this is my problem...

I have a existing b2b site... in english... the company is now expanding to french speaking countries. and the site needs to cater for this.

when logging in, the user selects a country. based on this selection, the site, from that point on, must be in that countries language. there are about 15 pages with text on them...

remember, I need to make it as easy as possible to add new languages later on.

I wanna keep all the text for al the pages in the database. I already have a cookie for the country the user is in. and most of the text are short strings like... "select a product" or something like that.

but what would be the best design for a database.

creating a table for each page with fields for each text string on the page and obviously a field for a language code

any ideas would be much apreciated... the main thing here is that it needs to be realy easy to add new languages later on.

hope your not as mentally constipated as I am today (it is monday after all)
thanx all

[Bullschmidt]

Well just to throw something "quick and dirty" out there for your consideration perhaps have a table with the following fields:
Language, PageName, ItemName

Thus the first page might have 30 different text items on it called such things as Par1, Par2, Par3, TextUnderMainPicture, etc.

Thus the table would only contain text and no formatting although maybe you WOULD want SOME formatting such as a bold word here or there (but if you do that you have to worry about malicious code being entered by someone).

And you wouldn't even try to put ALL the text from a page into one record (just one little item's worth)...

[Wintasy]

As always, "it depends" --on what exactly you are trying to achieve.

One approach (A) would be:

• PageID
• LanguageID
• PageContents

with PageID plus LanguageID as primary key; i.e., one record would contain the complete text for a given page in a certain language. (Sidebar: the PK should be PageID followed by LanguageID, and not the other way round, to make best use of the index in SELECTs.)

The advantage is that you can *quickly retrieve* an *entire page* from the DB, i.e., you don't have to "build" a page from "loose elements" in real time; the disadvantage is that it may be harder to maintain entire pages instead of loose elements --and whenever you want to make even the smallest change, you *would* have to change the entire page.

Approach (B) listed by Bullschmidt could also work, but personally I would be inclined to use a "functional" rather than a "structural" version --I will explain what I mean by that.

Instead of selecting "the first paragraph on page seven, in French", I would select "the welcome message in French", with table structure:

• ElementID
• LanguageID

with primary key ElementID and LanguageID.

The key advantages here are ease of maintenance, and reusability (actually, the two are closely connected).

You'd have an element "ProdSel" for which version "en" would be "select a product", and version "fr" would be "selectionnez un produit".

Then one day the site needs to become more "polite", so you change the elements to "please select a product" and "selectionnez un produit S.V.P.", respectively, and automatically all occurences of the elements on the site would change.

Two disadvantages are that it can quickly get very difficult to keep track of all the elements when you start to get a *lot* of them, and performance may be unacceptable when you have to build pages using lost of individual elements. (They need to be retrieved from the DB individually, too!)

One possible "best of both worlds" approach (C) would be to store everything in the DB using model (B), but then *generate* static HTML pages once, using the individual elements. This assumes that the information doesn't change very often, and you'd only re-generate again when the info has changed.

Hope this was somewhat helpful!

[spartan]

Thanks guys,
It all helps, I'm just looking for as many ideas as posible and you've already got me thinking of some new things...

I don't really want to keep it all in one table. But the only other option I can think of is having a table for each of the pages. would this not be better performance wise? I'm including a screen-shot of the first and second pages (yeah, its ugly I know, but its a intranet... not suppose to be pretty..)

let say now, I have a table for each page, then the table would look something like this...

Page1
-----------------

• LanguageID
• heading
• SelectCountry
• SelectAplication
• okBTN
• cancelBTN

that table would only have 1 row of entries for each language and when displaying the page I would only need to do...
rs.open "select * from page1 where languageID='en'",conn
and I would have easy access too everything that is suppose to be on the page and only that.

for page2 I would just do...
rs.open "select * from page2 where languageID='en'",conn

something to concider is the fact that, while I will need to add new languages every now and then, I will almost never need to add new functionality or text to the site. the actual site layout and content WILL stay the same...

so my question is... performance wise, would this aproach not be better than having to do *funny* selects and shuffling through recordsets to get to the data that you need?

[Bullschmidt]

I don't really want to keep it all in one table. But the only other option I can think of is having a table for each of the pages. would this not be better performance wise?

A table for each page sounds like a normalization nightmare. Tables are made to store data and be searched through...

[Wintasy]

Actually, there's nothing "funny" about SELECTs...

Seriously, what Bullschmidt says is right: it *would* be a "normalization nightmare". And you may be surprised to know that you actually don't gain much performance at all!

In your example, you have one table per page, and in each table you have one row (or "record") per language; the columns (or "fields") are the same in all tables. If that is what you want (i.e., you don't need some pages with a layout different from others), you could just keep the structure you have in mind, but instead of having one table per page, you add a column PageID, and you put everything in *one* table.

The primary key of that table is PageID plus LanguageID, and you select data as follows:

SELECT * FROM MyPages WHERE PageID = 123 AND LanguageID = 'en'

SQL Server is *extremely* efficient in retrieving data this way (as Bullsmidt says: "tables are made [] to be searched through"!); it will only need a *very* small number of disk accesses, even if you have *many*, many rows of data!

HTH

[spartan]

I get your Idea... only one correction. In my previous example I was suggesting having different field(collumn) names in each table. and making them descriptive of the text they resemble on the page...

But I'm getting what your saying, my picture in my head was not completely right, I had to go draw it on paper... (was it monday again yesterday? )

thing is. What happens when you have 1 page with 20 parabgraphs or text strings and buttons on it and another with only 5??

BTW... Wintasy... I've written some pretty damn funny, 2 page long, SQl statements in my life

[Wintasy]

Well, then you discover that you're using a non-normalized data model!

Some alternatives:

1) Put the text of an entire page in one row, as suggested earlier (fairly simple solution, quick & not-too-dirty, something I'd probably choose)

2) Pick some arbitrary maximum number of columns, say Field01 through Field50 that can either contain a string if it is needed on a particular page, or be NULL (very limiting and ugly solution, something I'd personally never choose)

3) Add another table, with parent-child relationship, so that you can have a 1:N relationship between a page and the elements on it (this will give you maximum flexibility)

The problem with 3) is that you need *ordered* elements (1st element on page 3; 2nd element on page 3; and so on), which complicates maintenance.

The primary key for your elements table would be PageID followed by ElementID and LanguageID.

You'd retrieve stuff using SELECT * FROM Elements WHERE PageID = 123 AND LanguageID = 'en' ORDER BY ElementID.

Then you'd move through the resulting recordset to "build" your page. (BTW, unless you need to store info at the *page* level, like Author, or LastModified, you don't even need that *parent* table, of course!)

The maintenance problem occurs when you want to switch elements around, or when you need to *insert* an element, since you'd have to modify the ElementID of existing elements. (But that would be the price you pay for flexibility...)

HTH

[Bullschmidt]

That's why I wrote this:

Thus the first page might have 30 different text items on it called such things as Par1, Par2, Par3, TextUnderMainPicture, etc.

So it doesn't matter how many items are on a page...

[spartan]

Thanx guys.
Wintasy... concidering the fact that the layout of the page is not going to change... I'm thinking option3 seem like the best one.

It just doesn't feel right to create a whole bunch of collumns that probably never get used, just because I have one page with a lot of elements on.

I'm going to do some thinking and discovery work around using the 1:N relationship between the page and element tables

Thanx again guys!
you got me thinking out of the box (took some frickin time though, heh finaly)

[spartan]

OK, in light of wintasy's (rather brilliant) idea... I have come up with this
*note* --> all 'ID' fields are PKs and anyfieldname with a 'ID'in it is a FK...

===================
Language 
-------------------
id  | name    
-------------------
1   | english 
2   | French     
===================


==========================
 Country 
--------------------------
id  | name     |languageID
-------------------------
1   | SA       | 1 
2   |Madagaskar| 2 
=========================
    

====================
 Layout 
--------------------
id  | type     
--------------------
1   | heading     
2   | Label
3   | button
4   | footer
====================
    


=====================
    Page 
---------------------
id  | description       
---------------------
1   | index.asp    
2   | propdoc.asp 
=====================


==========================================
     Page_Element 
-------------------------------------------
ElementID  | Description |PageID | LayoutID
-------------------------------------------
1          | heading     | 1     |   1
2          | footer      | 1     |   4
3          | heading     | 2     |   1
4          | footer      | 2     |   4
5          | okBTN       | 1     |   3
===========================================


=================================================
     Element 
--------------------------------------------------
id  | ElementID   | contents         | LanguageID
--------------------------------------------------
1   | 1           | my heading!!     |    1
2   | 3           | another heading  |    1
3   | 4           | my french footer |    2
==================================================

thus...in order to add a new Element to page1 I would add a new description for said element in the Page_element table... specifying the pageId it is for (1).

then add contents for that element in the Element table giving it languageID and a ElementID which is the element it is for.



comment PLZ!!

[spartan]

Thanx guys, I'm done and it works rather nicely. I've done all the code in a little include file. it uses server.mappath to get the filename and then selects the elements for givin page. I then put all the elements and there contents into a dictionary object so I can get to them easily without looping through a recordset everytime I'm looking for something.

like so...

<!--#include file="SQLcn.inc"-->
<%


'----------------------------------------------
'------ get the file name and directory -------
'----------------------------------------------
filename=Request.ServerVariables("URL")
pos=instrrev(filename,"/")
pos=instrrev(filename,"/",pos-1)   ' I need the file name and the direcotory... one level up
filename=mid(filename,(pos+1),len(filename))


'----------------------------------------------
'----------- set up local connection ----------
'----------------------------------------------
Set lcn=CreateObject("ADODB.Connection")
		lcn.Open strNewBuyingSQLcn
	

'-----------------------------------------------------------
'open recordset with all elements for this page and country
'-----------------------------------------------------------
Set rs=CreateObject("ADODB.RecordSet")
      rs.Open "SELECT e.contents as contents, p_e.description as description " &_
		"FROM element e " &_ 
		"join page_element p_e on p_e.ID = e.elementID " &_
		"join language l on l.id = e.languageID " &_
		"join country c on c.languageID = l.id " &_
		"join page p on p.id=p_e.pageID " &_
		"where lower(rtrim(ltrim(p.description)))='" & filename & "' "&_
		"and c.country = '" & Request.Cookies("nb_countryid") & "'", lcn


'---------------------------------------------------------------------------
'---- create a dictionary object to store elements and their contents ------
'---------------------------------------------------------------------------	
	Dim ElementsOBJ
	set ElementsOBJ = server.CreateObject ("scripting.dictionary")
	
	
'---------------------------------------------
'-- put all elements into dictionary object --
'---------------------------------------------
if rs.EOF and rs.BOF then
Response.Write "empty!!!"

else
	while not rs.EOF 
	description = lcase(trim(rs("description")))
	contents = trim(rs("contents"))
		
	ElementsOBJ.Add description, contents
	
	rs.movenext
	wend
end if


'---------------------------------------------
'--------------- clean up --------------------
'---------------------------------------------	
rs.close
lcn.close
set rs = nothing
set lcn = nothing


 %>

cheers
thanx for the help

[Bullschmidt]

spartan,

Glad we could give you some ideas and that you got something working...

[MarcusJT]

If you're happy with that approach then read no further. However, I would have thought that running that query on the database once every single hit would be INCREDIBLY inefficient, and completely unnecessary.

Why not use the code that I wrote for persisting a dictionary to an application/session variable (search my posts - it's there somewhere) to keep the retrieved data in memory and turn it back into a dictionary object on each page when required?

Just generate the data once for each language in the database in Global.asa (in Application_OnStart) and it's there for evermore! If the data changes, just regenerate it and update the app var!

Run some benchmarks if you're not convinced.....!


M@rco

[spartan]

Marco...
I'm very interested, but I cant find the thread... can you give me a hint (3 words... first word... 3 syllables...sounds like.... ) as to where it might be?

or give me a little more description or pseudo code so I can write it myself.

thanx alot.

[MarcusJT]

here you go!
(was a bit tired last night... long day)


M@rco

[spartan]

thanx man.

I'm gonna look into it rught now, I'm still playing with the idea of rather using templates. instead of storing the text in the database. rather create a freanch set of templates.

[MarcusJT]

But then if you make a change to the design, you'd have to update each template identically, which is madness!


M@rco

[spartan]

yeah, true but hink about it this way. If its just a small change, like, say... a layout change on one page, or adding a something. You know exactly where it is, and its only html, because the server side code is seperated. so you mke the change copy and paste to the other languges' template file and change the text to french or what-ever. thats not alot of work. And if it is a change to the code, you only change is it one place... if you want to complety redo the site, then you can keep the old templates and offer the user the choice of using the old 'look' or the new 'look'.

Its almost like running xml through XSL. you can have multiple XSL styles or design for one XML file. with templates you can have multiple html templates for the same server side code.

I like the idea of having completely different looking front ends for a site and being able to switch between them as I please... give your users a choice of what they want the site to look like...

[MarcusJT]

Oh, I'm completely with you on the templates, but it's not necessary to tie the languages into the templates in the way that you want to. Why not have one template for each "look" or "skin" of the site, and one set of translations for each language? Then you can have any combination without any code/data redundancy!!!


M@rco

[spartan]

agreed