#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    2
    Rep 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:

    wordID, word

    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
    union
    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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by snafu
    Is there a better way to design my tables?
    you're on the right track

    get rid of synonymID, it's not needed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    2
    Rep Power
    0
    Thank you!
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Location
    Little Rock, AR
    Posts
    7
    Rep Power
    0
    I am so old I still think about space and speed.

    Word, synonym.

    Read until you get back to the original word. One table.

    Dead fast and simple of you have direct access to your db.

    Joe Patterson

IMN logo majestic logo threadwatch logo seochat tools logo