1 Reply Latest reply on Jan 26, 2010 9:05 AM by philmodjunk

    Help with tables, relationships for simple multilingual glossary

    LanguageAmbassador

      Title

      Help with tables, relationships for simple multilingual glossary

      Post

      I need a little help setting up the relationships for a multilingual glossary where a record for a given word might have the same definition across the different languages, but the example for each is unique for each language.

      The design is so simple that I provide descriptions here in lieu of an attached file, with explanations of table names, fields, and relationships in parathesis.

      Glossary (main glossary table)
      Esp (field related to other field of same name of table EspGlos)
      Fra (field related to other field of same name of table FraGlos)
      Por (field related to other field of same name of table FraGlos)

      EspGlos (Spanish glossary table)
      EspId (unique identifier field, with values 10001 to 19999)
      Esp (field of actual Spanish words)

      FraGlos (French glossary table)
      FraId (unique identifier field, with values 20001 to 29999)
      Fra (field of actual French words)

      PorGlos (Portuguese glossary table)
      PorId (unique identifier field, with values 30001 to 39999)
      Por (field of actual Portuguese words)

      Defs (English definitions table)
      DefId (unique identifier field, related to EspId, FraId, and PorId)
      Def (field of English definitions)

      Exs (examples table)
      ExId (unique identifier field, related to EspId, FraId, and PorId)
      Ex (field of examples of words in sentences)

      As for functionality, I simply want a form with table "Glossary" at the top, displayed possibly with a portal. Under that I want a text field for the definitions and a text field for the examples. When you click on any word anywhere in the glossary at the top, which will be scrollable and sortable by each language, the value of the definitions text field and the value of the examples text field will change with respect to the word you click on in the glossary.

      Ultimately, this database will have many other tables that mirror the structure, relationships, and functionality of those of tables "Defs" and "Exs." I plan to provide pitfalls, idioms and more, but if I can just get the above-mentioned tables to work, I'm there. Then I can just pattern everything else accordingly.

















        • 1. Re: Help with tables, relationships for simple multilingual glossary
          philmodjunk
            

          I suggest you use one table for all your "glos" entries and add a field to identify the language to which it belongs. That structure avoids having to add more tables and relationships each time you add another language. To see just the entries for a given language, you can perform finds or use filtered portals. I also would not restrict ID numbers to a specific range for a given language, that seems like a needless complication.

           

          This gives you two tables:

          Glossary Table

          WordID (SerialNumber, primary key for table)

          Definition (Text field, english language definition)

           

          Gloss_Lang

          WordID (number, link to WordID in GlossaryTable)

          Language (Text to identify the language, English, Spanish, French, Portugese, etc...)

          Word (actual Word in given language)

          Definition (text, definition given in selected language.)

           

          You'd have one last table for examples, so that you can have more than one example for a given word:

          Exs

          WordID (Number)

          Ex (text field for example)

           

          With that table structure, it'll be much easier to set up the layout you want. Will it work for you?