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.
This is rather general question, but the solution itself is somewhat unique from project to project.

I would iterate over all records ordered by the time of their change (or a creating date or update timestamp or so). Next I'd match all entries with major fields similar: company name, address (though that might be risky), telephone or an url (parsing domains only). Then, I would recursively iterate over all found entries until no more results are found.

This algorithm would help to find you same entries as long as they do not have all major columns changes at once. If they do, there is no way saying it's the same firm programmatically.

This will link rows with seemingly now connections (rows 1 and 3 in example)

Example:

```
2001/01/01 Awesome firm, awesome.com
2002/02/02 Awesome firm, newaddress.com // linked with the first row over company name
2010/12/05 Ohsome inc, newaddress.com // linked over url

```