Database design for high concurrency and very large datasets

[egalp]

Hello all,
This is my first post here, and hopefully not my last since I need a lot of help.

I am developing a web application that is very large in scope and I have been contemplating for some time now on how to implement its database. Having worked on smaller projects in the past, i.e web-sites and portals with medium traffic, and some small web applications, I feel I lack the knowledge to successfully design a database that will scale for a large user base.

Basically this web application provides a work environment that houses different modules such as a to-do list, calendar, project management, etc, each having their own datasets, some requiring several tables.

The data is stored for two main entities - One is a user, like in any web application, and the second is a collaboration of users, which is a construct that defines data sharing structure between different users.

The straightforward way of building a database for those purposes is simply building a table per module, in addition to a user table and a collaboration table (plus a pivoting table, since users and collaboratio represent a many-to-many relationship).

However, I fear that under a large user-base (don't laugh..) of several hunderd thousands or even several million users, this could crawl down to a halt.
Different module tables will quickly grow to dozens of millions of rows, and they will become unmanageable (at least in MySQL, but it is the platform I have decided to work with).

I have considered making different tables per user or collaboration, but this is inefficient in the opposite direction - creating hundered of thousands of tables (and breaking normalization rules in the process...) I also considered creating different databases for different users and collaborations, but that sounds messy too.

If anyone has an idea on how to design such a database that will scale well with a large user base, I would be interested to hear your thoughts.

[448191]

Pictures speak louder than text: http://fabforce.net/dbdesigner4/

[egalp]

Thanks, I will give this dbdesigner a shot
Though I read its features I'm not completely sure what it does, it looks like a glorified ERD maker. Can it realy suggest database design? Does this project has any proven track record?

[egalp]

Or did you mean actually for me to make an ERD of my database...

[448191]

Sure, all I'm really saying is it helps to make a visual representation of what problem you're facing.

It's been a while since I used DBDesigner (2 years) but the thing I remember I liked about it is the ease in which it synchronizes with your database. If that makes it 'glorified' then yes, it is.  :P

It also supports ERD Crow's foot notation like Oracle's Designer.

Any visualisation of the design will help you, and help us help you.  :)

One could also use a class diagram. Using DBDesigner just makes it easy to apply the design (it can also reverse engineer).

[b]Edit:[/b] on the site of FABForce I read DBDesigner is no longer developed and will eventually be replaced by the official MySQL Workbench. Which I had a quick glance at: it still needs a lot of work.

[egalp]

Well I had a crack at the DBDesigner, it can't connect to my MySQL server, so basically it serves no more than helping geenrating ERD's for me.

Anyway, my problem is not optimization (yet) but the actual design, which I have not decided on yet and need help formulating.

Does someone has any idea on how to segment tables that contain millions of rows in a way that doesn't seem artificial and break normalization?