1

I am saving company information in a database. There is a single table with an auto-incrementing key that is unique to each company. There are separate tables for emails, websites, phone numbers, and company names. When a new company is added to the database, a check should be performed across the various tables to see if unique data already exists.

For instance, if a new phone number, email, and website are added, a check should to be performed to see if they are all already present in the database. If the phone number and email are present (a unique combination), then I want to grab the unique company id for those two entries and add a row to the website table indicating that the website is now associated with that company id.

However, if the phone number and email exist for one company id while the email and website exist for a different company id, I want to merge the two ids into one.

I personally hate this setup, but I can't think of a cleaner way to save all this data. For instance, it's not guaranteed a phone number, email, or website will always be passed. Moreover, some of the data is fairly subjective. I can easily query email addresses and phone numbers from multiple tables:

SELECT id FROM companies LEFT JOIN emails using(id) LEFT JOIN phones using(id) WHERE phone = "123" AND email = "john@smith.com"

However comparing organization names, a WHERE query won't return correct results. I wrote a function in PHP to compare businesses names using similar_text and soundex, but those can't easily be incorporated into a MySQL query. Similarly, one check I have in place to differentiate businesses is a longitude/latitude distance check. Consequently, my envisioned workflow (as of now) is:

  1. Loop through each unsaved company profile in PHP
  2. Get all saved, unique information from the database:

    SELECT id, email, position, address, zip, organization_name, phone, website FROM companies left join emails using(id) left join locations using(id) left join organization_names using(id) left join phones using(id) left join websites using(id)

  3. In PHP, loop through each row, attempting to find a match between the saved values and unsaved value.

  4. If a unique entry exists, update the database. If a unique entry does not exist, add the appropriate rows to the database.

I can see this being incredibly inefficient given the number of companies saved in the database, which is why I am here asking for help. Database design is not my forte, so any advice is appreciate.

  • 1
    This is a bigger problem, don't know if you can properly solve it by just SQL queries. – mustaccio Jun 17 '16 at 0:37
  • @mustaccio What-a-link, thank you for that. The two are definitely related, however my situation is limited to a known set of possible unique keys (for example, if G2 only applied to his car example). So while it is very difficult to determine "is this a duplicate," the fallback in my example would just be to add a new company if the very specific, predefined "uniqueness combinations" were not met. Basically, my example doesn't need to be anywhere near perfect (at least not yet ;-)). The question then is "what's the most efficient way of doing so"... – Charlie Jun 17 '16 at 1:06
1

My solution of this would be use relation tables instead of merging ids, use uniqueness for tables phone number, email, and website etc. and insert with IGNORE command like

Insert ignore into emails values (5,a.a@a.com);

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

or if you want o update on duplicates, use Replace instead of Insert ignore

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

This way you don't need to check for duplicates. If you want more complicated duplicate checks, you need o do it in the code, not in the database

  • Unfortunately this doesn't really answer the question. I really don't think the current database structure is efficient, and I'm looking for a more optimized solution. – Charlie Jun 22 '16 at 23:31
  • @charlie You wanted advices too, so i tried to give semi advice, semi answer, just tried to help – Abdullah Ahçı Jun 23 '16 at 7:54
  • I appreciate the help, I really do! It's just, as you said, a "semi answer" which to me doesn't seem worth the bounty. – Charlie Jun 23 '16 at 13:12
  • As I said, I didnt reply to get the bounty, just to help! – Abdullah Ahçı Jun 24 '16 at 11:19
  • I appreciate all the help :) – Charlie Jun 28 '16 at 20:04
0

Fuzzy matching made no sense, given the number of entries I would be dealing with. I needed something to quickly tell me whether or not a duplicate already existed. Consequently, I ended up creating hashes of all unique combinations for a single company. For example, if three different sets of information about Company A were obtained:

  1. Set one has hashes for phone+address+city and phone+name
  2. Set two has has hashes for phone+email
  3. Set three has hashes for phone+email and phone+name

The hashes for sets 1 and 2 do not overlap, but set 3 has hashes that are located in both sets. Consequently, they are the same entity. If set 3 did not come along, sets 1 and 2 would be considered separate since they had no unique data to link them together.

The actual database implementation for this is still being implemented.

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.