I’ve actually done something similar recently with a couple of databases, where I moved indexed files and folders on my local hard drive to indexed files and folders on iCloud Drive.
Step 1: Where I would start would be to create empty folders in the Finder with the structure that you want, and then index those folders in the database. If you have a nested folder hierarchy, then you only have to index the folder at the top level-the sub-folders will be indexed automatically.
Step 2: Before you start moving documents in the database, you will need to locate all the documents that you may already have indexed and move those documents into the database. This is easily done by creating a smart group Kind is any document and Instance IS indexed. Select all the documents in this smart group, right-click, and choose Move into database.
Step 3: Now you can just start moving documents from the non-indexed groups to the indexed groups.
Step 4: When you have finished, change the original smart group criteria to Kind is any document and Instance IS NOT indexed, select all the documents, right-click, and choose Move to external folder. Finally, delete the original (now empty) groups that are contained in the database. If you have no replicated documents, that’s all there is to it, for the most part…
If you replicate documents in the database, this will not be 1:1 possible. Indexing the documents as described above will preserve all the replicants you may have in the database, which is a big plus. However, the concept of replicants does not exist in the Finder. A replicant is NOT the same as a document alias in the Finder. Because of that, while you can have indexed documents replicated to indexed groups in the database, only one indexed folder in the Finder will contain a document that has been replicated to multiple locations in the database.
If it is important to you which Finder folder contains those documents, after you have done Step 3 you will want to move those documents to the external folders manually, then use the smart group process described above in Step 4 to batch move the remaining documents to external folders.
Once your database is set up and you add new documents in the database to indexed groups, you can always manually move them to the external folders or continue to use the smart group and batch process them that way.