single or multiple databases advice needed I know there are a few of these posts, but not in relation to my specific quandary.

I've always gone the 'per user' SQL Server DB rather than 'one DB for all' because:

a) I thought speed wise it's better for a lite user to only work within their small DB rather than all users being possibly affected by a few big users (surely searching a few hundred rows each time is better than searching through thousands of rows all the time).

b) If a server has too many DBs (very wishful thinking) then I can put the next lot of users on a 2nd, 3rd, 4th, etc server.

c) If a user no longer wishes to use my app or needs it restoring to a backup then I just bin/or restore their specific DB and don't affect anybody else.

My problem is that my next pet project involves pupils potentially competing against pupils in other schools and also showing a league table of schools so I just keep thinking a per school DB is going to be really messy compared to everybody in one DB.  Would you agree?

My fear of one DB is that later down the line there might be potentially 500 schools (again wishful thinking) with on average 300 pupils in each school which is a lot of pupil logins and results to manage in one DB - without it being lumpy.

Anybody had similar problems and overcome them?

Thanks
How much data are you dealing with? 500 schools with 300 students in each is not a lot of data for SQL Server to deal with if you design the tables well. As other users have mentioned in the comments, a few indexes on the right columns and you should be fine. SQL Server can happily run terabytes of data without working too hard.

The management of multiple databases is also much harder than it looks. A few examples:


Deploying schema changes to multiple databases when you upgrade your application
Backups for multiple databases - location, times, restore time, etc.
Running other maintenance tasks (checkdb) for multiple databases


The one point you mentioned that is harder is if restoring or deleting the data for a particular school is an important requirement. It is much harder to restore/delete data for 1 school if they are all in the same database, and that reason might be enough to mean you should split the data.

My general rule is if it's the same app then you should start designing it in 1 database until you have a reason to split it.