Managing customers in database

Question

My co-worker is having an issue managing customers in his data-base. The issue is when an order goes through the customer needs to be added to the data-base. But if the customer exists we don't want to make a duplicate customer we want to keep the same customer and just add to their sales history. He is having a hard time figuring out how to full proof that. Associates sometimes enter the sale, and can sometimes enter information not exactly as they did before so when his system checks to see if the customer already exists, it thinks its a new customer when in fact the customer already existed, but the info was entered slightly different.

Does anyone have any ideas about how to go about this?

Thanks.

Answer 1

Since the data being entered is similar but not exactly the same you want to do a Fuzzy logic search first, then if no results found do an insert of the new customer.

Answer 2

Add one more step as part of the order completion process. This step should do a fuzzy search on the customer records to see if it can find a full or partial match for a customer.

If it finds something close, display the records and ask the user to either pick one or go on to create a new record.

Obviously, you wouldn't want to expose that to the end customers. Instead just ask them if they've ordered before and to put in their log in information.

Answer 3

If possible, let the user log in.

This saves you the trouble with searching for a similar customer, and the users do not have to enter their address more than once.

Answer 4

Avoiding duplicate data entry is a common problem and can be handled multiple ways. The key I would focus on is that any successful solution must be easier for data entry person or it won’t be followed (e.g. telling DP to check if customer already exists doesn’t work because it takes more work on their part). Off the cuff I can think of several ways this problem has been addressed.

First solution is key based; have some check on unique information that has low probability of being entered differently. The most common case I’ve seen is an email address reduced to lower case text. Next common is phone number (though more common to have one phone number for a whole office or home). If the email address exists, then the customer must exist, so user can’t add them as a new customer, instead auto fill out the form based on data associated with that customer. Users like this because they only have to type in one piece of data and the rest automatically fills in.

Second solution, which can complement the first, is based on auto complete (http://en.wikipedia.org/wiki/Autocomplete); As user types in data for customer, your application provides auto completion for existing customer data that matches what is typed. This improves the likelihood tha user will select existing record rather than type in all new data. This allows, for example, someone to type in ‘john sm’ and see ‘john smith, john smart, john smuck, etc.’. Each field filled in narrows the selection for auto complete for subsequent fields (e.g. only so many john smith in Boston, MA …). Users like this because, again, it reduces the amount of work they have to do.

The third solution, which was touched on previously in another response, is to do fuzzy searches. This is a bit tricky to implement well, but very powerful. Many of the popular Enterprise Search Engines (e.g. Autonomy, FAST, Verity, etc) do this very well. But a clever programmer can also implement an in house solution. Key points include Use case insensitive search (so a search for Lafleur will match LaFleur) or use SoundEx (so a search for John will match Jon) See http://en.wikipedia.org/wiki/Soundex for more information.

Good luck,

Rick