December 13th, 2011, 08:20 AM
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.
December 13th, 2011, 05:39 PM
you're on the right track
Originally Posted by snafu
get rid of synonymID, it's not needed
December 13th, 2011, 07:41 PM
February 29th, 2012, 04:18 AM
I am so old I still think about space and speed.
Read until you get back to the original word. One table.
Dead fast and simple of you have direct access to your db.