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

    Join Date
    May 2012
    Posts
    2
    Rep Power
    0

    Non-hierarchical Reflexive N to N relations


    Hi

    I have a question on modelling a specific kind of database

    To better explain - I want to make a relation consisting of words which are connected to each other by meaning (like a thesaurus)..for this, I need to build such a model where in one relation i will have all the words with their ids, and in the other i will have which word is connected to which..

    now..what I want to know is which model for the second relation is better:

    a) duplicate connections so I only search from one index, like this:

    word1 - word2
    word1 - word3
    word2 - word1
    word2 - word4
    word2 - word5
    word3 - word4
    word3 - word1
    word4 - word2
    word4 - word3
    ..

    or

    b) dont add connection if it already exists in the opposite direction, search by both indexes, like this:

    word1 - word2
    word1 - word3
    word2 - word5
    word4 - word2
    word4 - word3
    word5 - word6



    Obviously the a) will make my search shorter but the database will get insanely big, while b) will make the database shorter but will prolong the search process..so which one of these is more efficient in a scaled version of the database where there's like 20,000 words or sth, in your opinions? I'm really in a quarell with myself on this one
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    assume each word is associated with 5 others

    then 20,000 words means 100,000 relationships

    storing 200,000 relationships instead of 100,000 is ~not~ "insanely" large

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    2
    Rep Power
    0
    huh well if it scales like that, and im not sure it does, then you're absolutely right

    thanks for your reply, i will try to do the math to see the difference in real numbers (didn't thought of it that way=

IMN logo majestic logo threadwatch logo seochat tools logo