To prevent duplicated name & best practice


1- Let say we don't want user to add duplicated company name in the system.
Using a search form, what will be the best practice to use for searching, How far should we go.
(the admin can set to make name and-or phone number verified before to add a new company)

What it does for now, if the system find an "Exact Match", it's prevent to add the company.

Right now if we search : "MyCompanyName"
this will not find "MyCompanyName Inc" exist
and will allow the user to add the company

When searching should the system ignore:
accent (in french we have éàî )
Special caracters like - space & _ . ,
Inc Lte

2- Another case when doing a "Regular Search" the system ask a minimum of 4 characters and "space" are considered as 1 characters to my opinion the space should not be counted
The system  allow to search "My  C"
What will be the best way to manage it?
 My Co = 4 not 5

I know we cannot control everything but I'm looking for a moderate way to prevent duplicate company name
LVL 28
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stu215Systems AnalystCommented:
Rather than restricting the search you should allow the users to search using wildcards '%userValue%' so that it will pull up a list of any portion of a company name and allow them to select it, and also provide them with an option of "other" if they cannot find it.  If other is selected then provide them with a text input which they can type the new company name into.

Before doing the insert of the new company name, do a select to check and make sure it does not exist or bring back any similar options and ask the user "are you sure you did not mean to select this instead?"

- If new company names do not come up frequently after you have an established list then you might consider having an administrative process where they request a new entry created that they can then select...

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
On my forms for this type of situation, I don't have an "add" button until you do a search.  I encourage users to start with just the first few letters and my results include a wild card without having a user to add a wild card.

A search for "comp" is sent to sql server as "comp%".  Depending on how many contacts they have, I will limit the results to something manageable.  20 to 30 results with a note to refine the search.

It does force the user to work a little and that comes with some consequences of them asking for a faster way to search or add people.  I'm working with contact db's with under 100,000 contacts and many have 10K to 20K.

There is no perfect solution, you do have to work it out with the project owner and go through the options from the easiest to add contacts to the most reliable way to ensure there no duplicates.  I also include a report of possible duplicates.  How the report is put together depends on the situation.    Typically, I may make a key by concatenating  the first few characters of multiple fields like company, address, city as example.  This would make "My Company, 1234 Maple Rd, Someplace" have a key, "My 123som".  Then find other keys that match.  You could also do this very thing when  you are about to insert new data.  Let them fill in the full info, then make up a key like this and give an alert with a listing of other possible duplicates and question if they want to proceed.

Because no one way is perfect, I also build in a merge routine that allows them to merge one company info to another and then archive the duplicate.

Probably the best way though is to use a 3rd party service to look up contact/company and perform some address verification and standardization before inserting to the db. Using address standardization will really help in finding duplicates.
stu215Systems AnalystCommented:
NOTE: by using wildcards i was referring to your query... The user doesn't need to know that you are using them... ( '%userValue%' ) where userValue would be substituted with what they actually typed...
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

lenamtlAuthor Commented:
I don't think this is possible to use wildcards in this case or maybe I missed something

Here more info about the way it's working right now

User enter a company name
Transport ABC
click search button, the word Transport ABC is frozen in the field and cannot be edited at this point

If the exact match exist it's display the information
If the exact match does not exist the Add button appear

Using this logic we cannot use wildcards for the search because the name that is entered before the search will be the one use to create the contact (frozen) and will the one that will make appear or not the Add button.

Note: We usually use wildcards for other search let say for report or to filter list, but for that case it seems not possible.
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Trying to search on exact match by a name is going to be tough.  Things can be spelled wrong or somebody may have added two spaces between word, Transport  ABC or maybe the company goes by multiple names UPS/United Parcel Service, FedEx/Federal Express, Or creatively spelled Bellwether vs Bellweather perhaps Transport ABC vs Trans port ABC.  

Using just the name, you are going to have to make changes to what you are doing.  If you want to keep the full name frozen on a form, then do an exact match search first and if you don't find anything, perform another search but add the wild cards programatically.  Personally, I find the first few letters are a good start and only place the wild card to the right.

Your results could show, "We did not find an exact match, but found similar company names..."

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stu215Systems AnalystCommented:
@Scott  - Agreed.

If you don't allow for some flexibility in the search/match then you will likely end up with a bunch of duplicates.  By making it easier for a user to select the correct one by presenting possible options prior to creation will decrease the likeliness of duplicates.  Most users will take the simplest path to achieve their objective and just create the new one under your present scenario if that means they can continue with what they need to do...

The only way to almost eliminate duplicates completely is to make creation an admin function where they are trained to not create dups... of course admins can make mistakes too... and then have the user select from a pre-defined list.
lenamtlAuthor Commented:
We will probably add a dropdown for company name extension (INC, LTE, Corp etc) in the first time and will take in consideration using wildcards and progressive search.

Adding a merge tool is also considered but not for now because of the complexity of the actual form.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.