1

I have a DB table with words and a description for them (kind of like a dictionary). These words can have synonyms and I want to enable searching for both the words and the synonyms giving as a result the 'main' word. What would be the best DB structure to implement this and make searches as simple and efficient possible?

For example: A word could be 'tree' and possible synonyms 'plant' and 'vegetation'. When searching for 'plant' the result should be something like:

Tree (plant, vegetation): Blablabla


Assuming that I also want to search on the word description would this change your answer?

The domain (in case it matters) is an Android App with a Sqlite DB.

P.S. I have seen questions such as Good database and structure to store synonyms but this is a little more specific.

Community
  • 1
  • 1
Omar Kohl
  • 1,372
  • 2
  • 15
  • 32

1 Answers1

1

Something like:

create table descriptions
(
   description_id integer not null primary key,
   content varchar
);

create table words
(
   word varchar not null primary key,
   description_id integer not null,
   foreign key (description_id) references descriptions (description_id)
);

(Not 100% about the syntax, I don't really know SQLite, but the above is ANSI SQL)

Every row in the words table with the same description_id is a synonym for the others.

You probably want to "normalize" the words before storing them (all lowercase, singular and so on).

  • This design has the flaw that there is no way to tell which word is the 'main' canonical word for a group for synonyms, but it could be solved by adding a boolean to the table words. Another thing is that (as you can see in my example) when showing the results I want to group words with their synonyms. Am I wrong in assuming that with this design it would be very inefficient to achieve this? – Omar Kohl Nov 30 '12 at 08:08
  • 2
    @OmarKohl: you didn't say you need a "main" word. But if that is required, I'd add a boolean flag too. I don't really know SQLite, but with PostgreSQL and other DBMS, the words could be retrieved using a join between the two tables and using string_agg(), to get a comma separated list of them in a single SQL. The other solution is to simply send two queries. One for the words, the other for the description. –  Nov 30 '12 at 08:16