Related words

Databases
1

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.

[b]data in wordTable[/b]

[COLOR="Blue"](id) word[/COLOR]
(1) diamond
(2) drink
(3) gold
(4) jewel
(5) poor
(6) wine

[b]data in relateTable[/b]

[COLOR="Blue"](word1) word2[/COLOR]
(1)       3
(1)       4
(2)       6
(3)       4

The 2nd way has only a table like the below.

[b]data in relateTable[/b]

[COLOR="Blue"](word1) word2[/COLOR]

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.

B[/B] Which way will be better?

B[/B] 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

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?

3

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

My mistake, I meant to type “smaller” :blush:

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?

5

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

Au contraire, I gave you the questions above to indicate the second option is better :slight_smile:

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)

7

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.