Multiple tables or one large database?

[drewjoh]

I'm curious about how to plan for scaling and I'm looking for some thoughts, advice or comments about the concept.

 

Say, for example, let's assume a web app like Basecamp with stats such as:

- 10,000 users

- 2,000 groups (accounts that multiple users can belong to, but they can only belong to one).

- 4,500 projects (multiple projects per group, can only belong to one group)

- 200,000 to-dos (belong to a project)

- 20,000 milestones (similar to to-dos)

 

Since different groups (and also projects, to-dos and milestones) will be accessed at varying levels (active groups vs. not very active groups vs. dead groups), I don't assume at all that it would be very efficient to put all this information in one database.  Searching through to-dos or milestones could just be insane, or would indexing compensate for that? (would just searching the to-dos in a particular project effectively, instantly narrow the searching to those records only? What about memory consumption and inserting and deleting rows?)

 

Or would it be better to have separate databases for each group? So only groups that are active are loaded or bothered with? Searching would effectively be quicker? Deletions and insertions would be faster?  But I can see things like looking up users in a backend could be incredibly trying. How do you manager all the groups, users, etc from a single location? What about having to initiate connections with lots of different databases as opposed to just one? Is that so much more resource intensive?

 

I guess I don't understand the technicalities of MySQL enough to understand how to figure these questions out.  Should I be looking for a different solution altogether for such a scaling?

 

From what I do understand, it seems like one large database would be the best solution assuming data amounts don't get out of hand. How much more of a strain does a 20GB database put on a server as opposed to lots of little 20MB ones?

[drewjoh]

Anyone have any thoughts?

 

After some more digging and thinking; I think I'll go with the multiple database route. I can see an added security benefit, faster searching for each group, and the ability to scale by putting different databases on different servers.

 

The only thing I can think of that would be a problem is keeping up with it all. Perhaps have a master database to keep track of users, global stats, etc.

[fenway]

That's depends if you need to pull them all together or not.

[drewjoh]

Yeah, I'd never need to pull them all together in the sense that we need all the records from all the tables merged with another database (or all the databases).

 

I'd only need some information from all of them to be able to administrate all the accounts/groups.

[fenway]

You talked about different permissions, etc. -- but you'll need a common login to all DBs, or manage multiple for each, which is difficult (though not impossible) to manage from the application code.

[drewjoh]

Yeah, that's a good point. I imagine I'll solve that by creating a "master" user table, so when a new group/user is created, it's added to the admin database also (and changed when groups/users are changed), and then have that linked to the individual database, with a global mysql user that can access them all if needed (from a coding standpoint).

 

Thanks for your input fenway!