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.
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?
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?
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.