Normalizing a database with "chained" entries
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 exa开发者_开发知识库mples 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.
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.
You're on the right track, but mind that there is a column limit.
- In your
MEANING
table, thekey
would be a foreign key to theWORD.key
value - this allows you to relate to the values in theWORD
table without needing them duplicated in theMEANING
table. - If you make it so
MEANING.key
is not unique, you can support infiniteMEANING.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
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'
精彩评论