Ok all you MySQL geniuses, I have what I think is an easy question for the more experienced developers out there.

I'm building a franchise business site using LAMP. This back-office application will serve up to 1000 businesses, each with about 1000 customers to track.

My question is... should we pile all the information into one SUPER Database, OR spawn a smaller Database for each business then manage them from a sort of console?

A SUPER database will need EXTREME fault protection as so many businesses depend on it. Many smaller databases may have better fault protection BUT may be harder to manage?

Any advice? Experiences? Gottchas? LIMITATIONS?

Thanks for your help,
NiseIdea

Depends on your business model. If you're an app service provider, and will be adding to your application over time, it will be significantly easier to manage using one database.

If you're selling a piece of software, and can charge individual customers for upgrades over time, you'll be better off with separate installations.

The decision should take the following business decisions into account:

1. Are you building a software service or a software product (learn the difference)
2. How simple should it be to deploy new customers? If this is a full-service solution, then the simplicity of application deployment is less important than if you want to sell a DIY product (e.g. an online service)
3. How often will you be adding new features to the product or service, and how readily available will these be to customers? If you're doing an ASP model, you can easily roll out new features to your entire base of customers. As separate installations, you'll have to version each instance and deploy updates as packages to each. The benefit there is you can charge for the update process.
4. How important is it to be able to report against all applications at once? This is a key reason some companies decide to use a single database. Of course, it's not necessary to do so if you need reporting, but if you go with separate DBs, you'll have to maintain a central server / DB that gets updated stats from each application periodically.

There's probably a lot more to consider, but that should give you a start!

yes, all of that is something to think about. we're leaning towards one big database with redundant back-ups. many smaller dbs still run the same chance of sharing a hardware failure on the same boxes. better to go safely bigger -plus the management would/should be better.

thanks for your advice