related words

[dotJoon]

Although there are hundreds of thousands of words out there, I pick only 6 words like the below for simplification.

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.

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.

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?

[ScallioXTX]

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?

[dotJoon]

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.

[ScallioXTX]

No, I am saying the opposite meaning.

My mistake, I meant to type "smaller"

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?

[dotJoon]

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?

[ScallioXTX]

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:

word1   | word2
------------------
gold    | jewlry
jewelry | diamonds

Looking for jewelry with a query like

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)

[dotJoon]

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.