CODEWITHSUNDEEP
phpmysqldatabase
green green grass
green green grass

Reputation: 549

MySQL migrating data to a completely different database structure and keep relationships intact

I have a database that needs migrating into a completely different structure (we're going from a custom CMS to a slightly modified Wordpress DB).

I can pull the individual tables and map them onto the new structure easily enough, but I'm struggling to find a method to preserve the relationships between tables.

For example in the old db I have a customer and basket table joined 1 to many from the customer table. (IE one customer - many baskets). Mapping these to the new db is simple (user, usermeta and a custom table). However the keys in the new db are completely different to the keys in the old db, so I cannot use them to relate the new records together.

How can I re-establish this relationship? (even broad terms will be helpful)

Upvotes: 1

Views: 279

Answers (1)

mlinth
mlinth

Reputation: 3118

The only thing that occurs to me is keep your old keys in "temporary" columns.

  • Create the customer table first, with new id column, keeping the old id column.
  • Now create the basket table (without the foreign key constraint).
  • Run an update query, to update the new basket key column with the new customer id, joining on the OLD keys (sorry, don't know MySQL update syntax well enough to give an example).
  • Recreate the foreign key constraint on the new columns
  • Drop the "old" columns

Hope that helps.

Upvotes: 1

Related Questions