Hi @willgriggs,
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.
Good luck!