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 (www.jigthings.com) (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

I don't know that there's a single best way to set up the database. I find that it's often a tradeoff in setting things up in different ways. However my first thought says you'd like want the different languages in their own tables.

As far as migrating your access database you should be able too. Here's a tutorial to convert access to mysql that I found. You'll have to set up the MySQL database first, but it looks like you can do everything else (including setting up the tables) from within Access.

If you do a search for something like 'convert access to mysql' there are plenty of other tutorials in case the one above doesn't work for you.

I would DEFINITELY advise against the 2 table structure you first mentioned. Adding columns is always more time-consuming than just maintaining data. If you create the table structure cleanly upfront, you'll be glad you did. So yeah, your 2nd example looks much better, but I'd need to see some mock data in there to really guage how solid the 2nd design is.

Hi Steven and Eric, Sincere thanks for your input - it is very kind of you to help. Thanks also for the link to the tutorial about converting access to mySQL which will undoubtedly be very valuable.

If anyone else would like to add ammunition to either my side or my developer's side then I would love to hear from you! Many thanks, Colin.

Count me in to the normalizing side.
Having the translated texts as column in the page table makes no real sense.
It will more an hassle than a benediction.

And just to add my 2 cents, and as living in a country with 4 official languages, please, let your user choose the language beside the geo localization.
I hate having page served in German because my country have 70% of peoples speaking German and no choice to change it.
Thank you not, south park central !

This could be adapted like this:

//defining the countries
table country:
  country_id
  country_iso

//defining the languages supported
table lang:
  lang_id
  lang_name

//linking the countries to several languages, and defining the country name in each languages
table country_lang: 
  country_id
  lang_id
  country_name

//linking a page to a country and a language
table page_content:
  page_id
  country_id
  lang_id
  page_title
  meta_keywords
  meta_description
  stylesheet
  content_1
  content_2
  footer_1
  footer_2

//defining your menus and sub-menus
table menus:
  menu_id
  parent_id  <-- for a sub menu, it points to the parent menu
  position

//translating your menus
table menu_lang:
  menu_id
  lang_id
  menu_text

Of course, it's just a quick and dirty proposition.
I just thought it out when writing it. No deep thoughts there, and I could be missing something.

Hi Thierry, Many thanks for your valuable input. Yes, I fully agree about the user choosing the language - I can understand that it is very annoying to have a language imposed upon you that is other than the one you want! You have my promise that we will build-in the functionality you list.

I am really pleased that I posted to this forum - you guys all seem to be on my side!! Seriously though, I need all the help I can get so any further input would still be appreciated. Many thanks, Colin

If you can give some mock data examples for each of the tables, I'm sure we can bolster your ammunition even further. I always work in test environments, mock data, etc. to make sure everything is crystal clear, so I'm very accustomed to thinking along those lines. Just makes it easier to wrap your head around the solution when you're looking at it in practice!

Hi Eric
It is extremely kind of you to help - many thanks. I have got a load of test data in "My" model in MS Access. Is there any way that I can post that into the forum? Alternatively, if you could point me to an example of how to format the data in the easiest way to understand then I will be pleased to convert it to that.