I have a very challenging scenario at hand and would like to hear suggestions on the solution I am considering implementing.

I have several databases that have similar (non-identical) structures and I am centralizing on one base, but these databases have their own ID structures (PKs will be the same), but by bringing it to a centralized base I will obviously have conflicts on account of 2 IDs with the same value (example company 1 with customer id 100 and company 2 with customer id 100).

First approach: renumber I can renumber (it was my first approach) but as I start the next company, I realize that the ids are already generating conflict again (as I centralize and put into production, so the last id I generated for example would be 5000 already no longer worth because the production base is already at 7000). Using this approach is the simplest, I simply pass a new number to auto increment and the process continues with a simple ALTER TABLE tablename AUTO_INCREMENT = value;

Second approach: key by company I thought of creating my own key, something like company id * 1000000 + original id, example company 1 id 100 becomes 1000100 but my auto-increment field should be extinguished and instead a trigger would be entered, but this trigger would be complex because it wouldn't be just put a sequence table, and at the same time i should know the company (something like select max (table) as x where company = NEW.id_company). This approach would be by far the safest as ids come to have their relativity with the company and because of this I never generate conflicts but I will have to keep generating the id for the rest of the process life. My question is if this will be performative

Tables don't have a lot of data, in the worst case scenario I'll be talking about 50,000 row tables, but by centralizing all the small parts on a single basis my performance may be compromised.

  • I wouldn't touch the company IDs at all. Building your new database with an auto increment column for maintaining uniqueness and perhaps a composite key for the company ID and something else that is unique to that company (if needed). I definitely wouldn't mess around with changing the auto_increment value or creating some new "synthetic" key. – Dave Jul 31 '19 at 13:55
  • Thanks @Dave for comment – MCunha98 Jul 31 '19 at 14:50

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.