Question: I have a directory of companies provided to me they want stored and updated in a MySQL database. There is no unique identifier such as company #1234 for each company record.

The fields are typical for a mailing list, contact name, company name, street address, city, state, zip code, phone number and type of company. Updates will be sent to me as a CSV file, again, with no company unique identifier number.

How do I go about matching up the stored record in the db to the new one so it can be updated? In this industry the contact name can change, and even the company name because they add and subtract partners. Their street address can change because when they move the business, and they can even change their phone number. The majority of the companies have a website URL, so hopefully that won't change often but it easily could as well.

I've seen in MySQL there is a similar match %, would this be the answer to match records with the new information?

I work in PHP, if there is a PHP solution. Thanks in advance to the kind soul who helps me out with this!

Answer: 
Without primary key, it is always tricky.

One line solution, decide the rules to best suite your requirements.

If I were you, I first would go to the client to decide some rules of identifying similar records. This step is necessary as without primary key, as there is always a chance of duplicate entry or updating wrong record.

Rules could be simple like:

```
1. Available fileds:
    contact name,
    company name,
    street address,
    city,
    state,
    zip code,
    phone number and
    type of company (I Hope this is industry)
2. We will first match company name for similarity like
    select * from table_name where company_name like '%$company_name%'
3. For all found records, match zip code and phone number. If match, break, record needs to be updated
4. If not match found in step 3, match street address. If match, break, record needs to be updated
5. & so on.

```

Your client is the best person to decide these rules as he is the owner of the product.

On the other side, asking rules from client is also important to keep you secure as **in the absence of primary key**, even after all the care, there is always a chance of duplicating records and/or updating wrong record. You could just minimize the chances with good rules.
I have come acroos bit similar scenario in one of my earlier projects in Sql server.I used to do the following things to handle it.

1.Usually there will be 2 types of files--

a)Full feed (frequency weekly) this will have all the companies from the providers database
b)Incremental Feed(Frequency Daily) this will have only the new records which are not in full feed and updates.(inserts-I,updates -U as flags in incremental feeds)

2.So once I receive the full feed I will refresh the my database table with the full feed once in a week.Also here I will have my internal ids to each company record.(thses ids are for internal purpose)

3.On daily basis I process incremental feeds based on the flags(I-insert,U-update).

4.One very important thing here is to manage the mapping table.Once the feed comes just assign a new internal id to it.

5.For comparing the data to avoid duplicates,I used to use Fuzzy algorithm to get all the potential matches and then use wildcard characters to filter and identify which are new and duplicates.