2

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.

4
  • It depends, however if you expect only medium data size (I.e. do not expect horizontal scale out), no software variation between tenants and you do not need strict data separation then having a single table is easier to manage and has a smaller footprint. But don’t worry too much, you can always change it.
    – eckes
    Apr 3, 2019 at 11:04
  • I am actually redesigning a project and we already have about 50 organisations running on previous project. It scales on an average 2 organisation per month and currently we are following the 1st approach i.e single database. Do I need to try 2nd one or should I go as it is?
    – Shashank
    Apr 3, 2019 at 11:13
  • Well does your current architecture work for you or not?
    – eckes
    Apr 3, 2019 at 11:18
  • It works but if it scales(in future) will it cause any problem? or what approach is better(optimized)? As I am converting it from traditional php code to a web service. I have to take a decision.
    – Shashank
    Apr 3, 2019 at 11:21

2 Answers 2

3

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.

2

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.