We currently have a solution for this in production, but it's pretty bad. I'd like a fresh perspective on it please.
We have a PERSON table with an ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, and SUFFIX_NAME. All these columns (except ID of course) are nullable.
What is the best way to index this table, and what is the best way to query for full name with wildcards?
This is used in a web app, and we need to allow the user to search for something like
and it return "Bob Smith", "Bobby Smithson", etc.
We will not allow the user to sort results.