I am about to design my database for my synonym web application. If someone is typing a word for example ”important“ you should get some synonyms like ”essential“ or ”necessary“. Since a synonym web app has typically thousands of rows I want to design my database to get the information as fast as possible. For my application I found two possible designs for the synonym database.

But I don't know whether they are suitable.

CodePudding user response：

You could use a normalized design.

Here every colun is indexed and would so deliver fast results

You could also implement a full texts search for word in wordlist

Further enhancements would be to don't use 1,2 and 2,1, but for that the query would be somewhat more complex, and you could insert them at the same time

CREATE TABLE Word_list (id BIGINT AUTO_INCREMENT PRIMARY KEY,   word varchar(50),
  CONSTRAINT constraint_word UNIQUE (word))

INSERT INTO Word_list(word) VALUES('important'),('necessary'),('essential')

CREATE TABLE synonym(id1 BiginT, id2 Bigint,
    FOREIGN KEY (id1)
        REFERENCES Word_list(id),
    FOREIGN KEY (id2)
        REFERENCES Word_list(id)
  , PRIMARY KEY(id1,id2))

INSERT INTO synonym VALUES(1,2),(1,3)

SELECT syn.word FROM synonym s JOIN Word_list syn ON s.id2 = syn.id
WHERE id1 = (SELECT id FROM Word_list WHERE word = 'important')

fiddle