Each approach has advantages and disadvantages, but I would default to doing things in a single database and segmenting users by foreign key.
The way to keep your queries fast is to have appropriate indexes on your tables. This can be its own craft and will take some learning.
If you go with separate schemas or databases:
- You’ll still need good indexes on your tables, that’s not avoidable.
- Doing queries that span multiple databases is trickier. (For example, if you want to have an admin panel that gives you an overview of your business.)
- By separating databases, you move complexity from your application layer to your deployment setup. You could setup separate credentials for separate app instances and reduce the risk of the wrong account accessing the wrong data, but it’s a fair bit more devops-type work.
- It’s somewhat easier to migrate one account to a different machine, if that’s something you might need to do often.
In general, for beginners, I’d say it’s safer to go the foreign key approach just because you’ll need to learn a small subset of things and run into fewer confusing challenges along the way.
Also, regarding your scale, databases are built to handle many millions of records. Don’t worry about that too much. :)
Also there are always managed database options like Amazon RDS or Google Cloud SQL. You’ll still be responsible for choosing good indexes, but they’ll take care of provisioning and making sure it doesn’t run out of memory, backups and such.