Normalizing a database with "chained" entries

Question

I'm new to database design, and I haven't found a definitive way to structure my vocabulary database. Irrelevant fields aside, the data I want to store is conceptually something like this:

Word 1

• Meaning 1 (1-n of these)
• Example 1 (0-n of these)
• Example 2
• ...
• Meaning 2
• ...

Word 2

...

Now, a Word is identified by three attributes: Wordname, Language, and POS (part of speech). I've set this up as a compound key. From what I've read, I gather that the meanings and examples should be in separate tables, perhaps something like this:

Word table

• Key
• Wordname
• Language
• POS
• ...

Meaning table

• Key
• Wordname
• Language
• POS
• Meaning (1-n rows per key)

Example table

• Key
• Wordname
• Language
• POS
• Meaning
• Example (0-n rows per key)

But this strikes me as a horrific amount of data duplication. Would it be better to abstract out the wordname-language-POS key into a separate table and give each row a single unique key? Is there some approach that's altogether better?

Thanks much.

Answer 1

In general, you can save yourself some headaches by generating a unique key for each row of each table, where the key is a simple integer, rather than actual data. Foreign key references are easier, and you don't have to deal with problems like "oops, someone misspelled a word, but that word is now part of a foreign key in another table!" Databases that enforce foreign key integrity can really make life difficult when key values change.

And, as you note, using words and other info as foreign keys is a lot of duplication. The whole point of normalization (well, one of them, anyway) is to eliminate duplication.

Most database engines will generate those keys for you, usually with a property called "identity." These databases will usually have an easy way to retrieve those keys programmatically, when new data is inserted. That gets more into code and implementation, however.

Answer 2

You're on the right track, but mind that there is a column limit.

1. In your MEANING table, the key would be a foreign key to the WORD.key value - this allows you to relate to the values in the WORD table without needing them duplicated in the MEANING table.
2. If you make it so MEANING.key is not unique, you can support infinite MEANING.meaning values

Example

WORD

• key (primary key)
• wordname
• language
• POS

Example:

key   wordname    language   POS
----------------------------------
1     'foobar'    'English'  idk

MEANING

• key
• meaning
• unique constraint on both columns to stop duplicates

Example:

key    meaning
----------------
1      'a'
1      'b'

If you want order of the meaning values, you'll have to define a column to indicate the order somehow - IE: meaning_id

Answer 3

I too would advocate a key which is an integer. Your tables then become very simple:

Word
KeyTable
WordName
Language
PartOfSpeach

Meaning
KeyTable
KeyWord
Description

Example
KeyTable
KeyMeaning Description

Given a word, you could then get all of the meanings for a given word relatively easily:

SELECT m.Description
FROM Word w, Meaning m
WHERE w.KeyTable = m.KeyWord
AND w.WordName = 'Example'

Examples for a given word are also fairly simple:

SELECT m.Description, e.Description
FROM Word w, Meaning m, Example e
WHERE w.KeyTable = m.KeyWord
AND m.KeyTable = e.KeyMeaning
AND w.WordName = 'Example'