How many databases to use for web system?

Does it matter how many databases I use in my web system? I am planning to have:

1. User information and related tables
2. Admin tables and all system tables
3. Reporting system
4. Audit logs of tables
5. User object tables like Photos, Videos, Comments
6. User API applications to read/write data.

Questions:

1. I am using MySQL and MagnoDB with cakephp. So if i implement above then i will use 6 databases in the system. Add backups so 2 of each then 12 databases total. Any advantage / disadvantage this way vs dumping all tables into 1 database? I assume t开发者_运维百科hesedays with sites like yahoo, amazon, facebook, etc having hundreds or thousands of databases is the norm OR are these all powered by 1 database but having multiple instances?

2. For lookup tables: Do i duplicate them in each database or 1 copy in the admin database is good enough?

3. Also if i have multiple instance of the same DB do i need to name them like DB1, DB2, DB3 or can i call them anything?

We are developing a local reviews website so expect lots of users eventually.

Everything that has relative information should be in the same DB.
So if all the things you have mentioned have relative info you would need probably 3 DBs: dev, prod, backup.
If some of that info is not related to anything else, that it should be in the separate DB.
As a developer, I always create a new DB for each new unrelated project. Otherwise, you create / add new features to the existing DB.

Use a single database. The problem with using multiple DBs include distributed queries (as pointed out already), plus overhead associated with each db server / instance, and general maintenance complexity.

What you want are tablespaces http://dev.mysql.com/doc/refman/5.1/en/create-tablespace.html

Consider with dbs like Oracle, the overhead per instance is 300-500mb+. Not to mention a new set of processes and separate buffer caches. You want a single, unified buffer cache to make the most of your RAM.

Partitioning using a database as the partition unit isn't saving you much, but will make a giant headache. MySQL can handle huge amounts of data (terabytes), as long as you design your schemas well and tune the storage. And use separate tablespaces.

Moving your app, and backup / restore should be simple too.

The only reason I create separate DBs is if there are multiple customers involved, or the project requires it. But it is usually not required from a technical point of view.

disadvantages: trying to do joins across multiple database

Any area that you might need to relate to another area should all be in the same db.