Alright, here's what I've come up with so far:

SET @id=(SELECT IFNULL(MAX(id) + 1, 0) FROM leads);
UPDATE leads SET id=@id WHERE id IN (
    SELECT id FROM (SELECT * FROM leads) AS something WHERE hash IN ("hash1", "hash2")
INSERT IGNORE INTO leads (id, hash) VALUES (@id, "hash1"), (@id, "hash2");

Basically, I find the max id and add one, then update all indices if a hash is repeated. Finally, I insert the new hashes and ignore any duplicates.

Is this good? I have no idea. It works, but that's about as much as I know.

I've come up with a (hopefully) better way to save unique company data, which now consists of unique hashes for each entity. Basically, there are multiple hashes associated with a company:

hash1: ABC Corp
hash2: ABC Corp
hash6: XYZ Corp
hash7: XYZ Corp

Consequently, I'm trying to create a hashes table that looks something like this:

hash (unique primary key), company_id (auto increments with the group)

For example, if I inserted the above example information, it should be reflected in the hashes table like so:

hash   | company_id
hash1  | 1
hash2  | 1
hash6  | 2
hash7  | 2
hash8  | 2
hash13 | 3
hash14 | 3

From there, things get a bit more tricky:

Say I want to add some new hashes, all to the same company but I don't know if the company is in the tables or not. To decide that:

  1. If none of the new hashes exist in the hashes table, I want to create a new company_id for that group and use that id for these hashes and in subsequent insert statements.
  2. If, however, any of the new hashes already exist in the hashes table, I want to merge all of the company_id's into a new value in the hashes table and any other table that has rows referencing that id. Finally, I want to add any additional data using the new company_id.

Say (case 2) I have these hashes to add:

hash1  | ?
hash6  | ?
hash17 | ?
hash18 | ?

The result, after inserting them, should be:

hash   | company_id
hash13 | 3
hash14 | 3
hash2  | 4      -- company_id modified 1->4, hash2 appeared in the new hashes
hash6  | 4      -- company_id modified 2->4, hash6 appeared in the new hashes
hash1  | 4      -- company_id modified, (due to hash2)
hash7  | 4      -- company_id modified, (due to hash6)
hash8  | 4      -- company_id modified, (due to hash6)
hash17 | 4      -- added, new hash
hash18 | 4      -- added, new hash

I've done some research, it seems like a grouped auto-incrementing column is possible. Similarly, it seems like I can use the result of a previous statement in a subsequent query. I should also maybe look at transactions?

As of now, my workflow looks something like this:

  1. Select all company_ids where the hash equals any of the existing hashes
  2. Select the largest company_id from the hash table
  3. If no company_id's exist from step 1, insert appropriate rows with an company_id of max(company_id) + 1
  4. If one or more company_id exists, change all rows with those company_ids to an id of max(company_id) + 1
  5. Perform steps 3 and 4 on any other tables that reference the company_id, writing most of the logic in PHP

As you can see, my method neglects MySQL almost entirely. I have a feeling I can use ON DUPLICATE KEY effectively here, and I'm wondering if there's a way to skip step 5. But regardless of if it's possible, does this database design make any sense? Part of me thinks I'm going to have to do a portion of this with PHP but I am trying to make this as efficient as possible (I will be handling a large amount of data with whatever method is chosen).

  • 2
    I've edited your question with an example. Please check and modify if I have a mistake. Or just roll back the change if I grossly misunderstood what you want. – ypercubeᵀᴹ Jun 23 '16 at 21:07
  • @ypercubeᵀᴹ What you added seems valid, I'm pretty sure we're on the same page. – Charlie Jun 23 '16 at 21:39

Your Answer

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

Browse other questions tagged or ask your own question.