General question of other users with large databases

We have been looking at this, and have sorted a (very!) long-winded way of doing things at http://www.specialoffers.at

Basically it involves a massive number of different databases and is structured thus:

1. Database for each merchant - .e.g.

24 Electric have their own "minisite" at http://specialoffers.at/004/

2. This database then produces a cut-down XML feed (Product Name, URL & Price), which is then imported by another "category" database, which is linked to another version of PT -

E.g. all 24 Electric Products are at : http://specialoffers.at/appliances/search.php?q=merchant:24%20Electric:

3. To bring it all together, there is another database, which contains all of the merchant details e.g. Name, Description, Image, Link etc, which is then queried (via another version of PT)

e.g.

http://www.specialoffers.at/appliances.php

Click on "[ View Special Offers and Compare Prices from 24 Electric ]", and it will take you to the "minisite" listed in point (1.) above.

This database also provides the merchant details for the "minisites".

With regards to database updates, a daily CRON is run to import approx 14% of feeds on a daily basis - this feed is then gunzipped, imported & then the cut-down version pushed on to the category databases.

The only problem with this thusfar is that when a network decides to play silly-beggars with feeds it requires a bit more thought than previously.

Massive thanks are due to David for his immense amounts of support in getting the site up over the past few weeks!

Thanks for the rundown! I like the phased imports idea - do 1/7th of your feeds each night rather than all feeds every night - certainly worth considering on a very large site.

Hi andyb back in again,
Thanks for the comments, I was thinking more about this today, and another solution I came up with is a 2 database setup, and then ping pong them, what I mean by that is to run the site from one database while the second one is being updated, then once the second database has been updated, swap over and run the site from the second database and then update the first and keep going that way.
Especially as when you do full imports you are effectively writing over all the existing data anyway.
I'm still trying to nut out the best solution but that is what I came up with today.
whether it is a workable idea or not, i'm not sure.

That would be entirely workable, as you would just need to change the database name in the config.php file.

Here's something I've been tinkering with lately, Not quite there yet but getting closer.

I have set up Master Categories I.e Electronics, family, food, etc... Each category has its own database. I have it set so the customer selects to search in X category. I figured that 400,000 products or less takes just less than a second, so 12 categories.. in theory I should be able to get 5 million products "thats in theory :)" But anyway It will and does to run much faster....

That seems to work fine, I am now working on the import.. so that the mapping will apply and will import into seperate databases,,, not sure how I'm going to do that yet..

To get the data the first time I just dumped the product DB and sorted the products by CAT the imported into each seperate DB... Not the quickest method.. I've been thinking about having everything imported into the Product DB then having another script work on sorting it all,, then comes the question of how to delist the products later,,, Anyone else try this approach?