What method would you use to set up the database?

Hello everyone, I have a question regarding the preferred way to set up a database for a SaaS that I'm building.

Basically, for each company account would you set up a new database and have all the company data stored in its own database (this is the way I am currently handling it), or have one database for every company and call all data based on an organization ID?

I think the pros about having its own database is that one company can have (for example) 350,000 invoices on the invoice table, and this will never affect, performance wise, a small company with only 360 invoices on the invoice table.

The cons of this approach, are the managing of so many databases down the line, specifically when making changes to the structure. I created a funcion that will take a query and start connecting to each database, running the query, then disconecting in a loop. But this is prone to errors when the queries get more complex.

So what method would you use considering we're looking for the accounts and therefore data to grow up exponencially.

Something like this?

How to Design Multi-Client Databases

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Use single db for now. one the money coming in you can use that money to hire people and upgrade the app. as a starter, it's not worth the hassle of having a separate db for each client.

I understand this. I have a question, in CI4 is there a way to perform a migration in all the databases specified in a file? Or would I need to execute the migrations manually for each database I have configured?

I agree with Mr White's reasoning here. The only reason I would consider separate databases is if you need it for some security protocol or concerns. With proper indexes in place databases can handle a pretty large dataset.

I don't believe CI's migrations currently can duplicate across connections. You can specify using a specific connection in each individual migration, I believe, but that is the most it can currently handle.

Support Development •  CodeIgniter 4 Foundations • Practical CodeIgniter 3  • CodeIgniter Tutorials