Searching a database of names.

Question

I have a MYSQL database containing the names of a large collection of people. Each person in the database could could have one or all of the following name types: first, last, middle, maiden or nick. I want to provide a way for people to search this database to see if a person exists in the database.

Are there any off the shelf products that would be suited to searching a database of peoples names?

Answer 1

You shouldn't need to buy a product to search a database, databases are built to handle queries.

Have you tried running your own queries on it? For example: (I'm imagining what the schema looks like)

SELECT * FROM names WHERE first_name='Matt' AND last_name='Way';

If you've tried running some queries, what problems did you encounter that makes you want to try a different solution?

What does the schema look like?

How many rows are there?

Have you tried indexing the data in any way?

Please provide some more information to help answer your question.

Answer 2

With a bit of ingenuity, MySQL will do just what you need... The following gives a few ideas how this could be accomplished.

Your table: (I call it tblPersons)
   PersonID   (primary key of sorts)
   First
   Last
   Middle
   Maiden
   Nick
   Other columns for extra info (address, whatever...)

By keeping the table as-is, and building an index on each of the name-related columns, the following query provides an inefficient but plausible way of finding all persons whose name matches somehow a particular name. (Jack in the example)

SELECT * from tblPersons
WHERE First = 'Jack' OR Last = 'Jack' OR Middle = 'Jack' 
  OR Maiden = 'Jack' OR Nick = 'Jack'

Note that the application is not bound to only searching for one name value to be sought in all the various name types. The User can also input a specific set of criteria for example to search for the First Name 'John' and Last Name 'Lennon' and the Profession 'Artist' (if such info is stored in the db) etc.

Also, note that even with this single table approach, one of the features of your application could be to let the user tell the search logic whether this is a "given" name (like Paul, Samantha or Fatima) or a "surname" (like Black, McQueen or Dupont). The main purpose of this is that there are names that can be either (for example Lewis or Hillary), and by being, optionally, a bit more specific in their query, the end users can get SQL to automatically weed-out many irrelevant records. We'll get back to this kind of feature, in the context of alternative, more efficient database layout.

Introducing a "Names" table. Instead (or in addition...) of storing the various names in the tblPersons table, we can introduce an extra table. and relate it to tblPersons.

tblNames
  PersonID   (used to relate with tblPersons)
  NameType   (single letter code, say F, L, M, U, N for First, Last...)
  Name

We'd then have ONE record in tblPersons for each individual, but as many records in tblNames as they have names (but when they don't have a particular name, few people for example have a Nickname, there is no need for a corresponding record in tblNames).

Then the query would become

SELECT [DISTINCT] * from tblPersons P
JOIN tblNames N ON N.PersonID = P.PersonID
WHERE N.Name = 'Jack'

Such a layout/structure would be more efficient. Furthermore this query would lend itself to offer the "given" vs. "surname" capability easily, just by adding to the WHERE clause

      AND N.NameType IN ('F', 'M', 'N')  -- for the "given" names
  (or)
      AND N.NameType IN ('L', 'U', 'N')  -- for the "surname" types.  Note that
        -- we put Nick name in there, but could just as eaily remove it.

Another interest of this approach is that it would allow storing other kinds of names in there, for example the SOUNDEX form of every name could be added, under their own NameType(s), allowing to easily find names even if the spelling is approximate.

Finaly another improvement could be to introduce a separate lookup table containing the most common abbreviations of given names (Pete for Peter, Jack for John, Bill for William etc), and to use this for search purposes (The name columns used for providing the display values would remain as provided in the source data, but the extra lookup/normalization at the level of the search would increase recall).