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?