Best way to store user-submitted item names (and their synonyms) Best way to store user-submitted item names (and their synonyms) database database

Best way to store user-submitted item names (and their synonyms)


The requirements you state in your comment ("Optimized searching", "Handling Synonyms" and "Autocomplete") are not things that are generally associated with an RDBMS. It sounds like what you're trying to solve is a searching problem, not a data storage and normalization problem. You might want to start looking at some search architectures like Solr

Excerpted from the solr feature list:

Faceted Searching based on unique field values, explicit queries, or date ranges

Spelling suggestions for user queries

More Like This suggestions for given document

Auto-suggest functionality

Performance Optimizations


If there were more attributes exposed for mapping, I would suggest using a fast search index system. No need to set aliases up as the records are added, the attributes simply get indexed and each search issued returns matches with a relevance score. Take the top X% as valid matches and display those.

Creating and storing aliases seems like a brute-force, labor intensive approach that probably won't be able to adjust to the needs of your users.


Just an idea.

One thing that comes to my mind is sorting the characters in the name and synonym throwing away all white space. This is similar to the solution of finding all anagrams for a word. The end result is ability to quickly find similar entries. As you pointed out, all synonyms should converge into one single term, or name. The search is performed against synonyms using again sorted input string.