Results 1 to 9 of 9

Thread: Newbie Question

  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Question Unanswered: Newbie Question

    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  3. #3
    Join Date
    Mar 2010
    Posts
    5
    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

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2010
    Posts
    5
    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

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    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.

  7. #7
    Join Date
    Mar 2010
    Posts
    5
    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!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2010
    Posts
    5
    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"!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •