Dual database implementation

[truedeals]

I'm working on building a store that has about 400,000 products that are updated every day. I've coded an application that updates the database and automatically creates the required categories, subcategories, etc. This application accesses zip files from an FTP site, loads them to my server, unzips them and then places all the data in the oscommerce database, while forming the category/subcategory structures according to the new data.

This update process takes about 30 minutes to complete. I'd really like to perform the update without taking the site offline for this length of time, so I plan on loading one database with the new data, while another database provides data to the site. Once the update is complete, I'd like to switch over to the fresh database. The swithing process should happen almost instantly. This way, the site could remain online almost all the time.

Can anyone that's had any experience in this area provide advice or at least give some pointers?

Thanks,
Bill G.

[GroverDill]

hi.

Switching from one database to another is technically as easy as changing the db address entries in the config file. The only thing that would concern me about this approach is you may lose recent updates made to your "old products" database, for instance, new customers and orders. It probably would not be funny to lose orders in this fashion.

Thus, if you want to use a separate db, I'd suggest that you create a script to make sure the "new products" db is correctly synchronized for orders and customers (and addresses and config... basically all non-product tables) at the exact moment you make the switch (interesting to note that all this overhead will make the process quite a bit less instantaneous than you'd probably like). Also, I'd still suggest that you schedule all of this during slow store hours, just in case...