A friend of mine has an...unique database issue.

Due to a lack of foresight, I’m in an awkward position.

I have 4 websites with databases that are designed for posting news, but they’re all on different topics. However, I plan to make a central website which details information from all 4 of these websites and would like to have these retreived in date order. Having a database on the main site and having the subsidiary sites feed off of this would have been the ideal solution, but I don’t necessarily think that’s possible anymore as if I tried to migrate to that system, I’d have lots of conflicting ID numbers.

Is there any way to have the subsidiary sites maintain their control over the databases on each site, but have them retrieved and sorted correctly on the central site?

Basically, I’m planning on having my site become one of the sites there (MODx), and the other sites are using a special CMS-ish script he made (powered by a database). Hence the "unique" issue here, since I’m not skilled enough to know how to solve this issue.

It really shouldn’t be all that difficult to combine these databases into one. You’d just need to think through the process a little bit and make sure that you have the proper columns to capture the different data schemas and you’re maintaining the unique info that you need. The most difficult issue might be making sure that linked tables within a database stay linked when moved to the new one (if you have tables like this), but there’s always a way to resolve that (I usually like to do bulk finds and replaces in a text editor and then import, for example adding 10,000 to all of the ids in the first database, 20,000 to those in the 2nd, etc., but it can also be done using MySQL commands, PHP, and probably lots of other methods).

If you’re concerned about maintaining the original unique keys from the database tables, you may have to add a few "translation tables" -- every time I interact with a web API or deal with another vendor’s unique key, I make sure I have a translation table that uses OUR primary and says what THEIR primary key is. It just gives us a lot more flexibility when it comes to looking things up... you have to do a few SQL joins, but I’ve found that you just can’t expect anyone else to provide you with unique primary keys.

ZAP is right... it’s generally not difficult to unify databases... provided that there’s a sensible and consistent way for all that data to live under one roof (in one table in this case?).