Let's say someone is coding a multilingual dictionary in PHP, and he wants to let people submit both words and definitions in English and Spanish, but wants to track it so they can be filtered. Tables thus far are as follows:

  • words
  • languages
  • definitions

Languages are in a separate table to allow for expanding later on. That being said, would it be better practice to create a separate table for associating words and definitions with their respective languages and join when querying, or to add a language column directly into each table referring to the id column in languages?

  • I think you should add the extra column to each table, in the way that sometimes a word come from english or spanish and sometimes they mean different things. I just take a quick look, maybe you could show us the relationships diagram for this tables.
    – eiefai
    Feb 6, 2011 at 18:38

1 Answer 1


If it were my decision, it would look like this:

words (wordID, word, languageID, date_of_addition)
related_words (wordID, otherword0
definitions (wordID, definition, order)
languages (languageID, languageName, languageIdentifier)
language_name_translations (languageID, languageTranslatedID, 
  So that this table can be joined back on the previous one to use the two ids 
  to show for instance that 2 (spanish) is presented to users of 1 (english) as 
  "Espanol" (really it's just because this is _my_ definition that it makes sense
  to me, you would work on your own definitions of course)
synonyms (wordID1, wordID2)
antonyms (wordID1, wordID2)
word_origins (originID, wordID1, wordID2, notes)

So I would suggest at this point that you go get a copy of OED and identify all the parts that they present in their listings. Find out things like related words and word origins and consider that every single entry is a list in some fashion or another, so every presented part should be an entity in a table.

  • Don't forget I've left off things like pronunciations and IPA and common misspellings (all things that I expect to see in a dictionary of high caliber)
    – jcolebrand
    Feb 7, 2011 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.