SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: related words

  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    related words

    Although there are hundreds of thousands of words out there, I pick only 6 words like the below for simplification.
    Code:
    diamond
    drink
    gold
    jewel
    poor
    wine
    If a user submits a word "diamond", it shows jewel and gold.
    If a user submits a word "drink", it shows wine.
    If a user submits a word "gold", it shows jewel and diamond.
    If a user submits a word "jewel", it shows diamond and gold.
    If a user submits a word "poor", it shows nothing.
    If a user submits a word "wine", it shows drink.


    I am thinking of two ways.
    The 1st way has two tables like the below.
    Code:
    data in wordTable
    
    (id) word
    (1) diamond
    (2) drink
    (3) gold
    (4) jewel
    (5) poor
    (6) wine
    
    data in relateTable
    
    (word1) word2
    (1)       3
    (1)       4
    (2)       6
    (3)       4
    The 2nd way has only a table like the below.
    Code:
    data in relateTable
    
    (word1) word2
    
    diamond  gold
    diamond  jewel
    drink    wine
    gold     jewel
    I guess
    that the 1st way will give smaller burden to DB as the data will grow more and more
    and
    that the 2nd way will give smaller burden to me because it is more semantic.

    (Question1) Which way will be better?

    (Question2) Am I on the right track for the purpose?
    Do you have another suggestion?
    Do you know any web pages relating on this issue?

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,039
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by dotJoon View Post
    I guess
    that the 1st way will give smaller burden to DB as the data will grow more and more
    and
    that the 2nd way will give smaller burden to me because it is more semantic.
    Interesting, so you're saying you think having two tables and joining one of them to another one twice, combined with a WHERE clause gives more burden to the DB than just one table you can query with just the WHERE clause and no joining at all? Why do you think that?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Interesting, so you're saying you think having two tables and joining one of them to another one twice, combined with a WHERE clause gives more burden to the DB than just one table you can query with just the WHERE clause and no joining at all? Why do you think that?
    No, I am saying the opposite meaning.

    I am saying I think having two tables and joining one of them to another one twice, combined with a WHERE clause gives smaller burden to the DB than just one table you can query with just the WHERE clause and no joining at all.

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,039
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by dotJoon View Post
    No, I am saying the opposite meaning.
    My mistake, I meant to type "smaller"
    Quote Originally Posted by dotJoon View Post
    I am saying I think having two tables and joining one of them to another one twice, combined with a WHERE clause gives smaller burden to the DB than just one table you can query with just the WHERE clause and no joining at all.
    And why do think that?
    What's easier, getting three pieces of paper, find information on them and glue them together where needed, or just find the information on one piece of paper?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    What's easier, getting three pieces of paper, find information on them and glue them together where needed, or just find the information on one piece of paper?
    If my understanding is right,
    You gave me the answer saying the 1st way will be easier by giving me the question above.
    Thank you for the answer.

    By the way, What do you think about the Question 2 above?

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,039
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by dotJoon View Post
    If my understanding is right,
    You gave me the answer saying the 1st way will be easier by giving me the question above.
    Thank you for the answer.

    By the way, What do you think about the Question 2 above?
    Au contraire, I gave you the questions above to indicate the second option is better

    As for your second question, you're pretty much on the right track. You could create a table with word1 and word2 as columns and create the primary key on both and than use a UNION ALL query to look at the relations from both ways. That takes up less disk space, but gets slower when there are more rows in it.

    As an example:
    Code:
    word1   | word2
    ------------------
    gold    | jewlry
    jewelry | diamonds
    Looking for jewelry with a query like
    Code:
    SELECT
     word2
    FROM
     my_table
    WHERE
     word1="jewelry"
    UNION ALL
    SELECT
     word1
    FROM
     my_table
    WHERE
    word2="jewelry"
    Will yield both gold and diamonds
    (the table is read from left to right and from right to left so to speak)
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  7. #7
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Quote Originally Posted by ScallioXTX View Post
    I gave you the questions above to indicate the second option is better
    Ah, really?
    It was my mistake in understanding your saying.

    We are NOW even on the matter of misunderstanding other's saying while I owe you some on the matter of Q n A.

    Your example code looks very cool.
    Thank you very much.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •