How to structure database(s) for larger applications?

[eluser]Skinnpenal[/eluser]
Hi!

I'm looking for some views on how to structure database(s) for larger web apps.

I guess 37signal's Highrise or Basecamp could be good examples; large amount of users, and potentially a large amount of data connected to those individual users.

Should there be one database "to rule them all", or should each user (paying customer) get its own database? – OR are there other ways one should go about it?

[eluser]pims[/eluser]
One database for each user is a crazy idea ;-)

MySQL is a powerful DB, and before you bring it to its knees, you'd have thousands of millions entries in your tables. Index properly, and I'm sure you'll never have to worry about scaling.

And as for, how to model your data, well, I tend to create a table which represent an object, and then add some tables to link the whole thing.

What kind of web app are you building ?

[eluser]Matthieu Fauveau[/eluser]
Hi Skinnpenal,

Basically there is 3 ways to go (from my point of view) :

1. Each customer gets is own database. More secure (customers data are not vulnerable to potential bugs in your code that could compromise privacy of data), but more difficult to update (you'll have to update all the database and that could be an issue with a certain amounts of customers)

2. Use a shared database. Easiest to maintain but need more focus on the code security to make sure data are kept private (require more testing to find potential vulnerabilities in the code).

3. Use "views" to achieve separation of customers data. Probably the way that will cost the less. Unfortunately in their current state MySQL's views are not very reliable and they do not update automatically when you change the parent table. So we are back to point 1 in regard to maintability/updatability. Still, this seems to be the best way if you are willing to use let's say DB2.

4. Use stored procedures. But I'm pretty much a newbie on that subject and I'm still not really sure how to do it.

Hope this little input will help you make a choice. I personally go for the point 2. Sure I spend more time on the code but I feel it's worth it.