The right database design for a web synonym application

NamingHH 0
Viewed 59

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.

id word synonyms
1 important necessary, essential
id parent_id synonyms
1 null important
2 1 necessary
3 1 essential

But I don't know whether they are suitable.

database-design mariadb mysql sql
2 Answers

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')
word
essential
necessary

fiddle

nbk0

If the only use for this dataset is to map from one word to multiple other words, then your first choice is fine. But be aware, that if you ever need to go the other way, it is messy.

Toss id as being wasteful and unnecessary and slowing things down (in this case). So... 2 columns:

word VARCHAR(100) NOT NULL,
synonyms TEXT NOT NULL,
PRIMARY KEY(word)
Rick James0
Related