Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 '11 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, 
                            languageTranslatedName)
  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.

share|improve this answer
    
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 '11 at 15:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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