Hi list. 2 cases I'm interested in: 1) Migrating data from one database to another 2) Distributing data over many databases, and later merging In what ways can you design tables to easier facilitate the above cases? I am aware of multi-master replication software, as described here: http://en.wikipedia.org/wiki/Multi-master_replication For this question, I'm more interested in schema design, so that a home-brewed database synchronization can perform synchronization. I have some experience with this. One of my previous projects was to reliably migrate data from one database to another, where the 2 databases had problems like: - Many foreign keys weren't enforced - Some fields needed special treatment (eg: should be unique, or behave like a foreign key ref, even if db schema doesn't specify it. In other cases they need to be updated during the migration). - Most auto-incrementing primary keys (and related foreign key references) needed to be updated during migration, because they are already used in the destination database for other records. - Many tables are undocumented, some fields have an unknown purpose - Some tables didn't have fields that can be used as a 'natural' key for the purpose of migration (eg: tables which only exist to link together other tables, or tables where there are duplicate records). I wrote a Python script (using SQLAlchemy and Elixir) to do the above for our databases. Are there any existing migration tools which could have helped with the above? (it would have required a *lot* of user help). Are there recommended ways of designing tables so that synchronization is easier? The main thing I've read about is ensuring that all records have a natural key of some kind, eg GUID. Also, your migration app needs to have rules for conflict resolution. David.
