Let's say I have the following class entities defined: Locale, User, Product, ProductLocales, and UserProduct. Here's the ERD for it: http://docs.google.com/Doc?id=ad2pvck7zk57_0tscm3ggb -- it has a typo, the name and description are varchars not integers.

A user wants to find all products in their locale beginning with "Dog", but in the user's native language. I have a few questions.

1) This search is going to happen frequently, but the User, Product, and Locales table don't get updated often. What's the most efficient way to do this search? Should I just use some custom SQL (where upper(name) like 'DOG%' and locale = 'en_US')? Or should I map to some Java collection? Can and should I cache this? If so, how and how would it invalidate?

2) Since internationalization is required, a search may not match if I convert some column data to upper and match on the upper cased query, right... I'm not that familiar with Internationalization. Would mapping to a collection first and then doing the search help with the internationalization?

I will be using Postgres as the database.

Thanks,
Bradford