A client has asked us to design a large system that will allow the creation of multiple copies of an e-commerce site from one central system.
Basically with each site created (up to 1000) a new set of database tables will be created. All the site will be run from one copy of the site, the copy being used would be determined by wildcard DNS.
My question is, is there an advantage to having a seperate database for each site over running one database with many tables
I would guess the advantages could be associated with A) less overhead per DB seeing as they are all broken apart (load balanace, sort of) and B) if the one DB goes down, gets compromised or corrupted, it only affects the one DB without screwing up the others.
I am sure there are more benefits to splitting up the DB's, but these two seem to me to be the most sensible and logical, at least from an admin and maintenance perspective.
That doesn't make any sense to me. MySQL has the ability to add slave servers for a situation like this. Sounds like someone is confused somewhere. Course it could just be me.
In this case one database is just as good as running many. Like any application that contains the same data structure it is better to have one central database. Like your example, say you have 10 forums, each run the same software, so your telling me, you are going to have 10 different admin panels and your application will use 10 different databases. If you are, I hope your not the administrators, what a nightmare. Why not just use one and setup your all your programs to access that one database. Your database can handle this, it's the script that maybe can't!
Would either have a performance benefit over the other?
All the sites would point to the same web root, the PHP would then work out what site is being asked for and pick the right database. The database would hold allt he styling information etc.