The most efficient sql scheme for finding names and lastnames

Question

I create a list of participants on my site, and I want them to be able to search each other by name and surname or one at a time. The trick is that the user can have several names, such as first names and then nicknames, also the person can have more than one last name, his maiden name, and then the last name after marriage.

When users fill in their first and last names, each user can have several names and last names, for example, there can be a person with three names and two last names: Eleanor, Al, El and last name: Smith, Brown.

Then if someone is looking for Ela Brown, Eleanor Brown, Eleanor Smith or any other combination, they must find this person.

My question is: how should I install all this in sql (mysql) so that the schema and search are efficient and fast? I didn’t want to reinvent the wheel, so I turned to the professionals and asked a question here.

Thanks guys,

PS I assume that the standard solution would be to have a user table, a fname table, a lname table, a userfname table with userid and lnameid identifiers, and userlname with userid and lnameid ids, but I'm not sure if this is the best way to do this and or not the search will be quick ...

Answer 1

Do you need to distinguish between first and last names?

I would suggest a Users table having a UserID
as well as some UsersNames tables with UserID and Name , one-to-many relationships.

If you need, you can also add the IsLastName bit to the IsLastName table (or just the LastName column, but the bit is better than imho) ....
But in this way you search in one table and can easily find user IDs, and also not limit the number of names that each user can have.

EDIT: You can easily take an input line and split it. Therefore, if someone put “John Smith,” you could look for both or both of the names simply by splitting the string and using it in the WHERE , using either OR or AND depending on your intended functionality.

Answer 2

The last time I did something like this, I processed each name into one column in the NAMES table. All names, first / last / middle. The second table contains a link to the person record in the PERSON table.

Thus, each NAME field is associated with one or more PERSONS records. If I search for “Scott,” I would find the name Scott in the NAMES table, find the links in the NAMES_TO_PERSONS table (/ PEOPLE?), And then return all the entries for that name. those. Scott Bruns, John Scott, David Scott Smith.

It worked very well with a bit of preprocessing.

Answer 3

Text search is what you need - use Lucene . I have used Lucene in several projects, and it is really amazing - not difficult to use and ridiculously fast.

Answer 4

If in your data model users can have several, but a limited number of names, then the easiest solution would be to create indecies for each column that stores the name type. You would add a field for first name, last name, nickname, maiden name, etc. This model would be more efficient than having a one-to-many name association.

You can also evaluate if there are general search requirements for the rest of the application or if you want the search more flexible. In this case, you can explore the use of the backend indexing process, such as Lucene or using full-text search . Initially, I will try to avoid this, if possible, because it certainly complicates the project.