3
\$\begingroup\$

I am saving company information in a database, and if repeat information is found, I want to update the overlapping rows. Some of the supported fields are emails, phone numbers, organization names, and latitude/longitude positions. One of the obvious issues I've had to deal with, however, is knowing whether or not an organization is a duplicate of an already saved one. Consequently, I came up with some basic combinations of unique information that distinguishes one company from another:

  1. phone number + organization_names
  2. organization_names + location
  3. email hash + website hash
  4. phone + email hash
  5. phone + website hash
  6. email hash + location
  7. email hash + organization name
  8. website hash + location
  9. website hash + organization_name

My workflow, as of now is as follows:

  1. Determine if any of the above unique combinations are satisfied by the new information
  2. Loop through each combination and create an MySQL query to check the database for duplicate entries
  3. Use PHP's multi_query to run semicolon separated select statements for the above queries. These queries look like this:

    SELECT id, jaro_winkler_similarity(normalized, "ORGANIZATION_NAME") AS organization_name_similarity0 FROM profiles LEFT JOIN phones USING(id) LEFT JOIN (SELECT id, normalized FROM organization_names WHERE normalized LIKE "%ORGANIZATION%" OR normalized LIKE "%NAME%"  AS likeMatches USING(id) WHERE (number="6316870100" OR number="123") HAVING organization_name_similarity0 > 85;
    SELECT id, jaro_winkler_similarity(normalized, "ORGANIZATION_NAME") AS organization_name_similarity0 FROM profiles LEFT JOIN (SELECT id, normalized FROM organization_names WHERE normalized LIKE "%ORGANIZATION%" OR normalized LIKE "%NAME%"  AS likeMatches USING(id) LEFT JOIN locations USING(id) WHERE normalized_house_number="110" AND normalized_street_name="WASHINGTON" AND zip="00501" HAVING organization_name_similarity0 > 85;
    SELECT id, jaro_winkler_similarity(normalized, "ORGANIZATION_NAME") AS organization_name_similarity0, (3959 * acos(cos(radians(40.82127)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-73.051872)) + sin (radians(40.82127)) * sin(radians(latitude)))) AS coordinate_distance0 FROM profiles LEFT JOIN (SELECT id, normalized FROM organization_names WHERE normalized LIKE "%ORGANIZATION%" OR normalized LIKE "%NAME%"  AS likeMatches USING(id) LEFT JOIN locations USING(id) HAVING organization_name_similarity0 > 85 AND coordinate_distance0 < 0.1;
    SELECT id FROM profiles LEFT JOIN emails USING(id) LEFT JOIN websites USING(id) WHERE (hash="93e6b0eff1d85b8b177fc44ce66cd1871dcb89b8") AND (hash="433833d572dc47f8576468a384ef8539a15f4031");
    SELECT id FROM profiles LEFT JOIN phones USING(id) LEFT JOIN emails USING(id) WHERE (number="6316870100" OR number="123") AND (hash="93e6b0eff1d85b8b177fc44ce66cd1871dcb89b8");
    SELECT id FROM profiles LEFT JOIN phones USING(id) LEFT JOIN websites USING(id) WHERE (number="6316870100" OR number="123") AND (hash="433833d572dc47f8576468a384ef8539a15f4031");
    SELECT id FROM profiles LEFT JOIN emails USING(id) LEFT JOIN locations USING(id) WHERE (hash="93e6b0eff1d85b8b177fc44ce66cd1871dcb89b8") AND normalized_house_number="110" AND normalized_street_name="WASHINGTON" AND zip="00501";
    SELECT id, (3959 * acos(cos(radians(40.82127)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-73.051872)) + sin (radians(40.82127)) * sin(radians(latitude)))) AS coordinate_distance0 FROM profiles LEFT JOIN emails USING(id) LEFT JOIN locations USING(id) WHERE (hash="93e6b0eff1d85b8b177fc44ce66cd1871dcb89b8") HAVING coordinate_distance0 < 0.1;
    SELECT id, jaro_winkler_similarity(normalized, "ORGANIZATION_NAME") AS organization_name_similarity0 FROM profiles LEFT JOIN emails USING(id) LEFT JOIN (SELECT id, normalized FROM organization_names WHERE normalized LIKE "%ORGANIZATION%" OR normalized LIKE "%NAME%" OR normalized LIKE "%firm%" OR normalized LIKE "%pc%") AS likeMatches USING(id) WHERE (hash="93e6b0eff1d85b8b177fc44ce66cd1871dcb89b8") HAVING organization_name_similarity0 > 85;
    SELECT id FROM profiles LEFT JOIN websites USING(id) LEFT JOIN locations USING(id) WHERE (hash="433833d572dc47f8576468a384ef8539a15f4031") AND normalized_house_number="110" AND normalized_street_name="WASHINGTON" AND zip="00501";
    SELECT id, (3959 * acos(cos(radians(40.82127)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-73.051872)) + sin (radians(40.82127)) * sin(radians(latitude)))) AS coordinate_distance0 FROM profiles LEFT JOIN websites USING(id) LEFT JOIN locations USING(id) WHERE (hash="433833d572dc47f8576468a384ef8539a15f4031") HAVING coordinate_distance0 < 0.1;
    SELECT id, jaro_winkler_similarity(normalized, "ORGANIZATION_NAME") AS organization_name_similarity0 FROM profiles LEFT JOIN websites USING(id) LEFT JOIN (SELECT id, normalized FROM organization_names WHERE normalized LIKE "%ORGANIZATION%" OR normalized LIKE "%NAME%"  AS likeMatches USING(id) WHERE (hash="433833d572dc47f8576468a384ef8539a15f4031") HAVING organization_name_similarity0 > 85;
    
  4. If a match is found, grab the associated company id and add new information

  5. If no matches are found, add a new company id and add the new information to the database

This, to me, seems incredibly superfluous but more importantly is slow. I'd like to condense my MySQL queries (I'm slightly ashamed by them, there has to be a better way to do this) into something more succinct, but I can't think of an appropriate way to do so. Similarly, I can't break if a match is found or a match isn't found, because data might be saved using a different unique combination which needs to be checked as well.

\$\endgroup\$
8
\$\begingroup\$

I would enforce uniqueness through unique indexes in the database table itself, not through making and evaluating a series of SELECT statements.

This allow you to GREATLY simplify your UPSERT operation to a single query. That might look something like.

/* Assume for this example there is a unique index on field_a and field_b */
INSERT INTO table (field_a, field_b, field_c) VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE field_c = 3

Now, the challenge here is that if your table has multiple unique indexes, you can get some unexpected behavior. So you probably need to reconsider your uniqueness criteria. Why would you check uniqueness of hashes of field combinations? This makes NO sense. Think in REAL WORLD terms about what makes a company listing unique. This probably doesn't include things like links to one or more websites on a related website table, or one or more phone numbers on a related phone number table, or one or more emails on a related email table, etc. All the fields you need to determine uniqueness of a company should all exist on the company table, so you can place a single unique index across them.

\$\endgroup\$
  • \$\begingroup\$ Thanks for the answer! You're very right, I need to come up with a better way to determine what's unique and what's not. Unfortunately, I'm at a loss in thinking of something better. Do you have any ideas? \$\endgroup\$ – Charlie Jun 20 '16 at 21:41
  • \$\begingroup\$ @Charlie You might want to open a different question asking for review of your database schema. Without understanding what information you are trying to store in your database, it is hard to give advice. \$\endgroup\$ – Mike Brant Jun 21 '16 at 15:05
  • \$\begingroup\$ I posted a similar question on DBA more oriented towards my design, but haven't received any answers. I figure it could be answered here as well, however? \$\endgroup\$ – Charlie Jun 21 '16 at 15:24
  • \$\begingroup\$ @Charlie Since there is a MySQL here, and since you would meet the criteria of having a working application/code that you are just trying to receive some feedback on, I think you code have a schema review here. \$\endgroup\$ – Mike Brant Jun 21 '16 at 17:50
  • \$\begingroup\$ That's what I think too, I'll leave it unanswered for a little longer to see if any other answers come in, but I appreciate your help. \$\endgroup\$ – Charlie Jun 22 '16 at 23:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.