Suppose an SQL database like PostgreSQL with a simple table of products:

id  | name        | type
10  | MacBook     | 1
11  | iPhone      | 2
12  | Boeing 747  | 3
13  | MacBook Pro | 1

type is an ID from a separate table product_types:

id | text
---+ ----------
1  | notebook
2  | smartphone
3  | airplane

There are several such tables in the database, let's call them master or enum tables (what is the correct name BTW?).

The web server using this database then understands these enums, and, sure enough, there will be a (Java) enum e.g.

public enum ProductTypes { 

And some logic to localize these values to show to the user: String localize(ProductType type, Locale locale). Therefore, at the end of the day, a user that prefers German will see the following line in the list of products:

Boeing 747 | Flugzeug

In reality, there is a mix of more than one "non-localized" column and more than one enum column:

Boeing 747 | Flugzeug | 420 Passagiere | 2 Decke | 747-400/-400ER ...

I would now like to build a full text search on top of such database, one that would index product names as well as product types, and of course - for each of the server locales. I am mostly interested in an example implementation for inspiration. Some questions in no particular order:

  • I need to tell the database all the localized versions of all the enum items, correct? What if the localized names are "content" (i.e. not hardcoded)?
  • I need to have one full text index per localization, correct?
  • What if the list of localizations is "content" again, i.e. not known beforehand?
  • Full text search is usually made on text like the product description for instance, not on the product attributes fields. – ForguesR Sep 11 '15 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.