mysql database concept - how to avoid duplicate registrations in a global/local setting

Question

a conceptual question:

• I have a global system which sales agents use to write orders for clients
• Agents can register a client locally (within their own instance of the system)
• a client can register himself globally
• Agents have access to clients based on client-ID and zipcode

Condition:
I cannot have duplicate clients in the system.

Therefore I need to check during registration, if a client already exists.

I can only make a check on zipcode, because client-ID is an instance-based identifier (a client may have ID-123 in instance A and ID-456 in instance B).

Problem:
Is there a way around showing existing clients with exactly matching zipcode to whoever makes a registration since I don't really want to display something like:

please select one of the following:
Zip 12345 - City: Bigcity - Company Name: some corp.
Zip 12345 - City: Bigcity - Company Name: some other corp.
Zip 12345 - City: Bigcity - Company Name: small ltd. 
or create new account

to whoever tries to register.

I cannot modify the global/local structure of the system, but I could add elements or identifiers if this helps.

I hope this is enough info to give some input. Let me know if you need more.

Thanks!

Answer 1

If you don't want to show this dialog for confidentiality reasons, then there is little you can do. The input data just doesn't allow to make any hard decisions programmatically. There could be a typo or something, so I wouldn't trust a program to tinker this together for me. I think the only thing you could do is putting new registrations on a shelf, and only add them to the global database, when they have been confirmed as non-duplicate. You could run a process once per hour that does this.

• Find all new registrations within the last hour.
• For each registration: find possible duplicate in the existing database.
• If duplicate exists: notify manager to handle this manually.
• If no duplicate exists: confirm the registration, add it to the database.

You could also write a small tool for the person responsible for handing duplicates, that merges the new registration with an existing client and does whatever is required in that case (e.g. notify the person who did the registration, sending the new client-id, etc).