Hi Folks

I am a newbie – it is my first post here – but I hope that you might be kind enough to help me…….

We run a website (Jigthings UK) (Sorry if the links not allowed) that is being completely redeveloped. The new site will contain about 60 pages and (this is crucial) each page needs to be served in three different languages – English, German and French. By the use of “Maxmind GeoIP” we can determine which country a visitor comes from and the idea is to then serve him/her with the relevant pages by querying our database.

The database is required to accommodate 60 pages or more with initially 3 sets of locale based content. Further locales may be added to a maximum of 20, though 6 is a more likely scenario within a 2 year timescale. Page count may rise or fall arbitrarily.

The 60 pages are accessed via 14 main sections, with a submenu structure per section. All content and menu sections (main) and subsections are locale specific and are built on the fly.

The question is “How do we structure the database so that it is both Normalized and easy to work with?”



My developer has suggested putting the data in just two tables as follows:

tablePages (approx 60 pages = records)
page_ID_PK
mainMenu_ID_FK
pageFilename // add path in code to construct menu hotlink
engSubMenuLegend //Locale 1
engSubMenuDisplayOrder
engTitle
engKeywords
engDesc
engContent1
engContent2
engFooter1
engFooter2
gerSubMenuLegend //Locale 2
gerSubMenuDisplayOrder
gerTitle
gerKeywords
gerDesc
gerContent1
gerContent2
gerFooter1
gerFooter2
fraSubMenuLegend //Locale 3
fraSubMenuDisplayOrder
fraTitle
fraKeywords
fraDesc
fraContent1
fraContent2
fraFooter1
fraFooter2
//etc in blocks of 10 fields per locale in this example

tableSectionOrMainMenu (approx 14 records)
mainMenu_ID_PK
sectionName
sectionStylesheet
engMmDisplayOrder //Locale 1
engMmLegend
gerMmDisplayOrder //Locale 2
gerMmLegend
fraMmDisplayOrder //Locale 2
fraMmLegend
// etc per locale

Developer notes:
Locales can be added by extending the tables with a pro rata block of locale fields and an extra conditional prior to the query. 'N' initial locales will be available as required.



Now, as I understand it, each row of the tablepages table would contain data for a single page and provision would be made for additional countries (as and when added) by the addition of new columns within the table.

The tableSectionOrMainMenu table is required for the pupose of developing menu/submenu legends to appear at each page.

My developer makes the point that this table structure would facilitate the easiest possible entry of data but accepts that each time a new country was added we would have to create additional columns to accommodate the data. He also accepts that each query would necessarily by accompanied by “Conditions” that dealt with the locales issue (i.e. query would access different column names for different countries)

It seems to me that:
1. The suggested tables are not Normalized and consequently contravene “Best Practice”
2. A structure could/should be developed that does not require new field names (columns) to be added each time that we add a new country.
3. A structure could/should be developed that, as far as possible elminates the need for conditions that precede the queries. We would always have the parameters of “Country” and “Page” to hand prior to running the query and I think that with just these parameters we ought to be able to manage without conditions (to do with locale) that accompany every query.

My knowledge of databases is very limited but I have built the following in MS Access and it seems to work OK. The problem is that we are using MySQL and I don’t know how it will migrate – if that’s the right word!

page (table)
page_id
page_name

country (table)
country_id
country_name

country_page (table)
country_page_id
page_id
country_id
main_menu_id
page_title
meta_keywords
meta_description
stylesheet
content_1
content_2
footer_1
footer_2

main_menu (table)
main_menu_id
main_menu_position
main_menu_text

sub_menu (table)
sub_menu_id
main_menu_id
sub_menu_position
sub_menu_text

Any advice on what to do would be very much appreciated. Many thanks, Colin

seems to me that you are in the right track and your developer is way off base. these tables as you described will work on any platform.
Dave

Hi Dave, Many thanks for your reply. Any other suggestions or views would be greatly appreciated so that I can take them to the developer. Best wishes, Colin

easy question for the developer to answer if he rates his design

what happens if "we" decide to serve pages in Italian, Russian, Hindi or even Chinese?
will his model support that, and if not what would be the cost between going down his route, and going down your route

what happens then if you decide to add another 10 languages?

multi language can be a problem especially when maintaining the data over time.. ie someone makes a change to language 1, but language 2 & 3 are someone elses problem and don't get down at the same time (or in some cases ever).

so personally I like to see some form of timestamp indicating when a change has been made to one language and whether that change should be replicated in other languages. the problem is changes may not necessarily trigger changes in other languages . ferinstance you may change some locale specific to correct typos or say local distributor info, or it could be the product text has completely changed and the other languages needs changing as well. so its nice to have an audit feature built in which could alert the maintainers of each language that soemthing may need to be done.

Im alwasy suspiciosu when I see things like content1 & content2 in a page.

another possibility use to use placeholders in your web page design eg %placeholder1% and replace that placeholder with the valid text as required

Hi healdem, Many thanks for your considered response. I fully agree - it seems that the "Structure" of the database should be able to support an ever-increasing number of countries with the least possible hassle!

Your point about maintaining data in several different languages is well made and, I must confess, is something that I had not considered. I can understand that a change made on any one page may, or may not, need to be reflected on corresponding pages in the different languages. Clearly, each time a change is made it needs to be date-recorded and we need internal systems that will flag-up whether or not the change needs to be effected in the "Alternate language" areas. Thanks for that.

Before I face my developer again next Monday, I would really appreciate additional views that favour either me or the developer! Thanks in advance, Colin

Well it shouldn't come down as a "me vs. developer" what it should come down to is practicality and scalability.

If you look at what the developer is proposing and think that it isn't normalized and would not scale well and he can't understand what is wrong with the design, i'd think of replacing the developer myself before you go too far down a wrong road.

Hi guelphdad, I take your point but.....

I really admire the rest of the developers design work - he does a great job in Photoshop and writes impeccable HTML and CSS that validates throughout. It is just this database design aspect where we seem to be at odds with each other. I get on really well with the guy and, to be fair to him, it is the first time that either of us have come across this added complication of handling several different languages simultaneously. That's why it is so important to me to get as many views as possible - to ensure that we go down the right path together!

so he may well be a very competent web designer (HTML, CSS and so on), but that doesn't necessarily make him a good db designer. its a different skill set. sometimes you will find people who are good at both, or good in one and reasonable in another. that doesn't negate his skills, it may mean either you or soemone else needs to develop the DB side and let him play to his strengths

thanks healdem, I am sure that is sound advice. I am beginning to understand just how many skills are required to be an all-round "Website designer"!