December 13th, 2011, 07:20 AM
Join Date: Dec 2011
Time spent in forums: 20 m
Reputation Power: 0
Database design question
Hello, I'm an oldbie that was dusted off and recruited to design a lexical database. I'm a bit rusty, perhaps another oldbie or young gun can give me some input.
Since this is a lexical database I have an table of words. Let's keep it simple a say it looks like this:
Now I need to show a relationship between words for synonyms (at this point let's not worry about antonyms). So, for example, I need something that can represent this:
car is a synonym of automobile
car is a synonym of motor car
automobile is a synonym of car
automobile is a synonym of motor car
motor car is a synonym of car
motor car is a synonym of automobile
My first instinct is to create a table called Synonym which looks like this:
synonymID, wordID1, wordID2
(wordID1 and wordID2 are FKs to wordID in the Word table)
To find all possible synonyms for a word I'd have to do a union query:
select wordID1 from Synonym where wordID2 = wordID
select wordID2 from Synonym where wordID1 = wordID
All this doesn't seem very elegant to me. Is there a better way to design my tables?
Thanks in advance.