I have a large amount of data on users (about 8 million users).

I want to mine this data to create a table with statistics about first names. It should also include as many variations of each name as possible. The variations of first names ("John" and "Johny") should be recognized and treated properly.

The purpose of this table is to quickly find variations for a name and select one, in order to use user's non-standard name when contacting him. The name to be searched for can be his standard/ official ("John") or any variation of it ("Jack, Jackie, Jacky").

So far, I came up with following table structure:

table name: first_name_variations
|  name   | group_id | is_standard | count |
| Jack    |        1 |           1 |    53 |
| Jackie  |        1 |           0 |     5 |
| Francis |        2 |           1 |    32 |
| Frank   |        2 |           0 |    52 |

The "name" column should be primary index. Standard/ official names are to be marked with "is_standard". The variations of one name must be assigned the same group_id.

Should I include surrogate key (e.g. id)? Will it improve something?

Should I create a separate table for name groups, or maybe just marking names with group_id would be sufficient?

Since my database knowledge is rather shallow, can you please propose improvements to his approach.

The database is MySQL

UPD: I think I'll remove 'is_standard' column; it's an overkill.

  • Rick James, It's an attempt to make more personalized emails. The users are subscribed and always free to unsubscribe.
    – JackHammer
    Jan 27 '19 at 1:16
  • 1
    When a cold call comes in (a salesman going thru the phone book), and says "Hi, Rick, how are you today", I want to hang up on them. If they say "Hi, Richard, ..." they definitely don't know me, and should not be so chummy. "Hi, Dick" in German is something like "Hi, Fatso".
    – Rick James
    Jan 27 '19 at 2:55
  • You ask for improvements. Why ? Is your original design not performing well enough? Are queries taking too long ? How long ? And how long do you expect them to take ? What hardware is this running on ? Jan 28 '19 at 11:48
  • @AlbertGodfrind Just wanted to have pro-tips from experienced people before I can make mistakes. Fixing mistakes in database design is harder than fixing regular code bugs.
    – JackHammer
    Jan 29 '19 at 0:58
  • Sure. But you do have a reasonably sized data set to play with, I imagine. So why don’t you try out the alternatives ? Without any test case, measurements and observations, everything anyone proposes are just speculations. Jan 29 '19 at 5:27

Adding a surrogate id will not help in this case. You already have a good PRIMARY KEY(name), correct?

The 4 columns look good for the purposes mentioned. You probably need a composite INDEX(group_id, name) to facilitate finding all the related queries.

What do you do about nicknames that apply to two standard names?

What do you do about someone with a non-"standard" name as their 'official' name?

  • When nickname is ambiguous, I would just skip it. What did you meant by "non-standart" name as officail name"? Example would be good
    – JackHammer
    Jan 27 '19 at 1:39
  • @JackHammer - I don't have a specific example, but I can come close with my name. Richard=Rich=Rick=Ricky=Rickie=Dick. They are all based on Richard. But what if someone were born as "Rick", not Richard? Ambiguous: Frederick=Fred=Rick? Seems like Meg or Beth or Bess is also ambiguous, but I forget.
    – Rick James
    Jan 27 '19 at 2:51

With PostgreSQL

The right way to do this, is to use Hunspell and a database that supports it (like PostgreSQL). Essentially what you're trying to do is to implement a stubber in a relational database. That's no fun. You can do this easily with Hunspell and a custom affix file, or a custom dictionary. You can then add that to PostgreSQL.

There is actually already an affix file from SCOWL (Spell Checker Oriented Word Lists) that covers the basics.

For example Jack, will cover Jackies, Jacks, Jackes, and Jacks (with the SCOWL lvl 95),

After you have the dictionary set up, PostgreSQL will easily tell the count, and proper indexing is a breeze (it will work in search)


Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.