This is a good question, and there are two main approaches.
Which approach you should take depends on links between tables: If table A links to table B, but you only clone table A, you’ll end up with a new table A* linking to the old table B. If you then clone table B to create B*, the links in A* aren’t going to be magically redirected - the A* table still points to the old table B.
So, if you want to migrate linked tables, it’s best to do it all at once. If tables aren’t linked, you can migrate them piecemeal.
Option 1: One big migration
When you have multiple apps sharing tables with a nest of interrelated links, they should be cloned together. This goes as follows:
-
Identify the set of tables you want to migrate together - that’s anything that could possibly link to or be linked by anything else, across all your apps.
-
Create one big database (I recommend doing this in a new app), and add all your tables to it.
-
Clone this database. This will produce a new big database, with new versions of all your tables.
-
In each of the apps that use your tables, create a new Database. (This will, by default, create a bunch of empty tables. You’ll need to delete them carefully - don’t get the real ones by mistake!) Then add the tables your app needs from the new big database. Repeat this for each app.
-
Done! You now have new databases in your existing apps, referring to the new versions of each table!
Option 2: Piecemeal
If your tables don’t link to each other - as with @stefano.menci’s big “Logs” table - then you can go piecemeal. He could clone an individual DB or app into Faster Storage to create a new faster “Logs” table, then remove the old Logs table from each of his apps and add the new faster “Logs” table to each of them, without migrating any of his other tables.
The advantage here is gradual migration. The disadvantage is that if you break table links, you’ll have to reconstruct them by hand (or write a script), and it will be t-e-d-i-o-u-s. (And if you have mixed apps that write data into the wrong tables, you could have a real tangle on your hands.) Rolling this approach back is nearly impossible: Even if you have an Enterprise installation where you can do point-in-time rollback at any moment, the backup-restore machinery restores a database at a time, which doesn’t help you if your problem is cross-DB links. Plan carefully. Definitely the “experts take care” version.
If you’re not sure what to pick, choose Option 1.
Do a dry run first!
If you have production apps with production data, you will want to dry-run this migration! Fortunately, all of the actions here are non-destructive: They create new copies of your data in new tables. You can leave production pointed at your original tables while you move. This means you can practice your migration, test out the resulting apps and data, and verify everything looks good before cutting over and doing it for real. As long as you haven’t written any data into the new tables, rolling back is as simple as switching each app’s published environment back to the old database.
For more on environments and how this all works, check out the Deployment Environments section of our docs: