[MySQL] Verschillende databases "mergen"

[NickThissen]

Ik heb een aantal websites draaien die elk gebruik maken van hun eigen MySQL database. De websites zijn in principe los van elkaar ontwikkeld en hebben weinig met elkaar te maken, op een belangrijk punt na: de user accounts voor een website komen vaak ook in de andere website voor. Dat wil zeggen, mensen die een account op website A hebben, hebben er ook vaak een op website B.

Omdat de websites los van elkaar ontwikkeld zijn is er echter een klein verschil tussen de accounts tabellen in beide databases. Bij website A bijvoorbeeld was het niet verplicht een email adres in te vullen, maar bij B wel (dus de email adres velden in database A zijn niet compleet). Bij website B was het verplicht voor elk account ook je echte naam op te geven, wat weer niet in database A zit.

Ik wil nu graag dat de websites gebruik gaan maken van een centrale database. Ik wil de databases dus eigenlijk mergen. Eigenlijk zijn alleen de user accounts tabellen een probleem hier, verder hebben tabellen in database A niks te maken met tabellen in database B dus die kunnen gewoon naast elkaar komen te staan.

Het probleem is dus de users tabel, die moet tussen A en B gemerged worden. Elke user heeft in de database gewoon een ID (autonumber), waarmee ik de user aan andere zaken uit andere tabellen koppel (bijvoorbeeld user 1 hoort bij groep X en Y, dat wordt dus gekoppeld via UserId en GroupId zoals gewoonlijk). Het probleem lijkt me nu duidelijk: Piet heeft een account op website A en op website B, maar die accounts zijn helemaal los van elkaar en hebben dus een ander ID. Als ik nu de tabellen gewoon blind zou mergen dan raakt Piet dus de helft van zijn data kwijt (afhankelijk of hij het ID uit database A of uit database B krijgt), de koppeling raakt verloren omdat de IDs niet meer kloppen.

Gelukkig is er ook goed nieuws: elk account in de users tabel (in beide databases) heeft verplicht nog een ander numeriek ID (dit is een ID van een andere website die niet binnen mijn controle valt, noem dit maar even ID2), en die is ook uniek per gebruiker. Op deze manier kan ik dus gebruikers van elkaar onderscheiden, ook al is er geen echte naam of geen email ingevuld, als ID2 gelijk is dan is het gegarandeerd dezelfde user.

Het is nu logisch om sowieso het niets-zeggende autonumber ID te laten vervallen en gewoon dit ID2 als unieke key te gebruiken per user. Dat heb ik eerder niet gedaan omdat mijn ORM dat lastig maakte, maar dat mag nu geen excuus zijn natuurlijk.

Ik vraag me eigenlijk af wat hier nu de mogelijkheden zijn. Ik kan vast wel een (aantal) queries verzinnen om de users tabellen met elkaar te mergen op ID2, maar dan raak ik dus nog steeds de koppeling met andere tabellen kwijt. Hoe zorg ik er nu voor dat ik deze koppeling kan behouden? Is hier een goeie strategie voor of wordt dit een drama? Moet ik "handmatig" (via queries) alle ID koppelingen gaan vervangen ofzo?

[Barryvdh]

Kan je niet een script schrijven die dat doet?

Dus bijvoorbeeld alle gebruikers aflopen van database 2, kijken of deze ook al bestaat in database 1.
Zo niet, maak je dus een nieuwe account aan. Anders combineer je de gegevens en pas je de gebruiker in database 1 aan zodat die compleet is.
Daarna in database 2 de koppeling overzetten, door het gekoppelde userId aan te passen van het userId in db2, naar het nieuwe id van db1.

[Zoijar]

Barryvdh schreef op dinsdag 19 februari 2013 @ 10:03:
Daarna in database 2 de koppeling overzetten, door het gekoppelde userId aan te passen van het userId in db2, naar het nieuwe id van db1.

Daar moet je juist zo mee uitkijken: stel je past userid 5 aan naar het nieuwe id 1, en er bestaat ook nog een andere id 1 in de database, dan mergt de data van die twee... vervelend.

Weet niet of het het beste is, of het makkelijker kan, maar ik zou gewoon even tijdelijk een nieuwe kolom toevoegen overal met het nieuwe id, en de nieuwe foreign key. Dan kan je daarna alles linken aan nieuw-oud id en de nieuwe FKs invullen. Vervolgens wis je de oude kolommen.

[Barryvdh]

Zoijar schreef op dinsdag 19 februari 2013 @ 12:07:
[...]

Daar moet je juist zo mee uitkijken: stel je past userid 5 aan naar het nieuwe id 1, en er bestaat ook nog een andere id 1 in de database, dan mergt de data van die twee... vervelend.

Weet niet of het het beste is, of het makkelijker kan, maar ik zou gewoon even tijdelijk een nieuwe kolom toevoegen overal met het nieuwe id, en de nieuwe foreign key. Dan kan je daarna alles linken aan nieuw-oud id en de nieuwe FKs invullen. Vervolgens wis je de oude kolommen.

Ja maar ik bedoel juist, je hebt nu 2 accounts:
Barry (id 5 in Database 1)
Barry (id 24 in Database 2)
Nieuwe situatie dus alleen id 5 in database 1. Maar dan zoek je naar groepen gekoppeld aan id 24 in database 2, en die pas je aan zodat hij gekoppeld is aan id 5 (het nieuwe ID)

[Zoijar]

Ja, en als je dan ook nog user Kees (id 5 in Database 2) hebt?

[NickThissen]

Op die manier gaat het inderdaad mis.

Echter heb ik daar denk ik een oplossing voor: het ID2 dat elke user heeft (wat momenteel niet de primary key is, maar dat wel kan zijn). Als ik eerst in beide databases voor elke user de primary keys vervang door ID2, en dat dan door bereken aan alle gekoppelde tabellen (dat zou via Cascade moeten kunnen automatisch toch?), dan kan ik daarna alle tabellen uit beide databases in een nieuwe database gooien en zou de koppeling correct moeten blijven. Of ga ik nu iets doen wat heel 'vies' is?

[Big Womly]

In je nieuwe table hou je de oude id's bij zodat je ze kan linken met de juiste (oude) id's in de verschillende databases.

Id	username	DB1ID	DB2ID
1	Barry	5	24

Rest mij alleen nog de vraag. Ben je 100% zeker dat BARRY in database 1 dezelfde BARRY is in database 2?

Voorbeeldje
Gebruiker 1, Jan Maes, krijgt een username in database 1: JMAES
Gebruiker 2, Jeroen Maes, krijgt een username in database 2: JMAES
Gebruiker 1 krijgt nu ook een username in database 2, alleen JMAES is al bezet, dus krijgt hij bij DB2 de gebruikersnaam JMAES1...

Probleempje?

[NickThissen]

Dat is geen probleem want de situatie is nu als volgt:

DB1

Id	Username	Id2
1	Jan Maes	49204
2	Jeroen Maes	59828

DB2

Id	Username	Id2
12	Jeroen Maes	59828
38	Jan Maes	49204

Na mergen:

Id	Username
59828	Jeroen Maes
49204	Jan Maes

Id2 is een numeriek ID uit een third party website waar ik geen controle over heb, maar wat wel gegarandeerd uniek is per gebruiker. Daar kan ik alles dus op koppelen zonder duplicates te krijgen.

Het probleem was nu dat ik de referenties naar de Id (niet Id2) in oude tabellen moet vervangen naar referenties naar Id2. Dat is me echter vrij eenvoudig gelukt door die foreign keys met 'on update cascade' te definieren, Id veld veranderd naar de waarden in Id2 en alle referenties volgen netjes mee.

Het is nog even afwachten tot ik alles getest heb of het wel goed is gekomen maar een eerste (korte) indruk was goed. Ik heb het nu echter vrij handmatig gedaan, een tijdelijke database aangemaakt om af en toe wat tabellen naar weg te schrijven enzo. Dit wil ik niet nog eens gaan doen, ik weet niet of ik met een scriptje gelukt was (vast mogelijk maar ik loop dan toch wel tegen problemen aan...)