What is better – having 10 databases for 10 organisations or 1 table for 10 organisation with org_id? [duplicate]

I am making a web service (using Laravel in case that is relevant) that will have many separate organisations generated by the service.

Considerations

• I can manage all organisation data in one database by assigning an org_id column in each table, but when a row is fetched it will have to be inquired from all organisation rows (which I think will take more time).
• Another solution is to make separate database for every organisation, but then I think I have to switch databases frequently and for each request causing more database connections and in turn causing more server processing involved.

Question

So I want to ask what approach will best suit my needs.

it all depends.

from my personal experience, if you have one database for each organisation, all the data is neat and clean and it is all in one place for each different organisation.

In case there are changes specific to a particular organisation it will affect only that particular organisation database – as the changes may or may not be welcomed for the other organisations.

On the other hand, it might cause extra overhead if for each new organisation you will need to create a new database, and that involves making sure you have space for backups, and scheduling time to run all the typical database maintenance routines, etc.

Basically different databases give you more flexibility (even the permissions set could be different) but it also might cause overhead.

There are strong points and not so good point in either approach.

Logical (row based) separation will cause problem if the single table gets congested or too big to maintain. It will also cause problems if you need different variants of the same table or if you have multiple tenants and you want to enforce access isolation (with different dB account users).

However, since you not yet have the need I would not implement it just yet, but I would plan the schema and queries to be easily partitioned (with minimal need for global index) or sharded. (Typical example if you shard by creation date you better keep that around besides the primary key for lookups)

And then you can start small by keeping the Orgid but in addition to allow each tenant to specify the datasource. This way you can have 2 Schema holding 50% of all records and in an emergency single orgs can run dedicated schema.