Creating independent databases will create the risk that as data structures change with the application, structural changes (and any associated data migrations) must be propagated to all of the databases. This can get tricky. A school_id
in the tables that need it will simplify things in the sense that you only need to maintain one database.
Multiple databases would make more sense if you are going to deploy the application to physically separate clients (such as if the application runs independently on a server in the school), but will make maintenance more difficult.
Another place where it might make sense to have a separate database for each client would be if you know there will be slightly different versions of the data structures for each client. Of course, changes to common structures would still have to be propagated to all variant databases, and then you have to ensure that common patches/upgrades to the application work properly with the variants.
In general, I'd try to keep it all in one multi-tenant database, unless there is a very good reason to split it up.
Often in a multi-tenant system there is little or no sharing (in fact it is often desirable that other clients can be completely unaware of the others' existence) in which case the simple context_id is sufficient. It is generally sufficient for a hierarchical tenant structure (companies within groups, resellers, etc.) too.
For simple sharing this might work:
ThingTable AccessControl
============ =================
ThingID (PK) <---------------------------- ThingID (PK, FK)
Owner (FK) --. .-- TenantID (PK, FK)
Property 1 | Tenants | AccessLevel
Property 2 | ============= |
... `--> TenantID (PK) <--'
Property N Name
etc...
Where AccessLevel
could be a simple boolean representing read-only/read+write, or a more complicated collection of properties. You could even remove the "Owner" column from your tables unless you want it as a convenience and include ownership as part of the data in the access control table, or if you have no concept of an owner the objects you are modelling you can do away with it completely (it is usual for the "owner" of an object to have greater control, such as being the only user/group able to chance the access control for other users/groups, but this can be implemented in your access control table without needing a specific owner column).
If you are tracking multiple types of Thing this way, you will need to abstract out the concept so that you have a single entity to point the foreign key in AccessControl at, like so:
ThingTable1 AccessControl
================ =================
ThingID (PK, FK) --. .-- ThingID (PK, FK)
Property 1 | | TenantID (PK, FK) --.
Property N | | AccessLevel |
| | |
| | |
ThingTable2 | Things | |
================ | ================= | | Tenants
ThingID (PK, FK) --+--> ThingID (PK) <--' | =============
Property 1 | Owner (FK) -----------------------------+--> TenantID (PK)
Property N | Shared Property 1 Name
| etc...
|
ThingTable3 |
================ |
ThingID (PK, FK) --'
Property 1
Property N
Any properties common to all the "Thing" tables would go in the general "Thing" table along with the owner (if yuo are are noting the owner that way, of course).
You can get much more flexible than this: allowing for groups of tenants (from simple groups as used in Unix-a-like filesystems through to supporting hierarchical relationships), having control of which tenants or groups can see each other, ans so forth - but from your description I'm assuming such extra features would be overkill for your current needs so there would be little point complicating the design to include them.
Best Answer
You should use one table per object of interest. That means one table for users, one table for pages, one table for posts, etc. Use a normalized database (See database normalization) for transactional data. This is precisely what relational database management systems are built to do. Don't presume you are going to have performance problems because you have many rows. Most systems perform better with many rows than with just a few.