0

I have an application which populates a text file with information in CSV format. The problem is, the number of columns for each records should be flexible and it's not constant. I am trying to implement the same datastructure but in MySQL for faster reading and parallel writing from many nodes.

Here is a sample taken from my CSV File

like|love|adore|admire
hate|dislike
revive|resurrect
info|information|pieces of information|data
shoot|snipe|kill|hunt

Each record represents words with the same meaning. Later on, I get the synonyms of a word using that CSV file. For example, I get the synonyms for the word hate which is dislike using a function in my application that parses the csv file and outputs the matching words.

What options do I have to do the same implementation approach but using MySQL?

pjmorse
  • 9,204
  • 9
  • 54
  • 124
dlock
  • 9,447
  • 9
  • 47
  • 67

1 Answers1

1

You are dealing with many-many relationships here. The off-the-top-of-my-head solution would look something like:

CREATE TABLE words (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    word VARCHAR(40) NOT NULL UNIQUE
);
CREATE TABLE synonyms (
    word_id INT UNSIGNED NOT NULL,
    synonym_id INT UNSIGNED NOT NULL
    PRIMARY KEY (word_id, synonym_id)
);

Then, to get all the synonyms for word $X with an id of $Y:

SELECT w.id, w.word
FROM synonyms s
JOIN words w ON (s.synonym_id = w.id)
WHERE s.word_id = $Y;

This way you could also add antonyms at some point.

Sjan Evardsson
  • 454
  • 4
  • 4
  • but notice that my approach was based on the fact that a synonym for synonym is a synonym as well. That's why I made the words with the same meaning to be on one line. Using your approach, How would I be able to add new synonyms for a word and its synonyms? In other words, If I have "a|b|c" that are synonyms to each other then I want to add word "d" as a synonym for "a", it should be added to b and c as well – dlock Feb 07 '13 at 17:44
  • 1
    Your program would have to do the select before you added a word to the list. In other words, you say you want to add word d as a synonym for a. You select the synonyms for a, which gives you b and c. You then add the word d to the words table, and add the a|d row, the b|d row, and the c|d row to the synonyms table. – Gilbert Le Blanc Feb 07 '13 at 19:36