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.