Multiple companies in one database or one company per database?

I prefer to use just one database where you store all the companies.

i would also go with one database ...

@ivan.loliver @tomi Oh okay, can I ask why? Thanks.

@greenroofcorp I’ll give you a reason: if you get 100 hundred customers on board you don’t want to be migrating, scaling, supporting, etc. 100 databases.

@martinbean maybe we'll add a company_id column per table? is this approach okay?

@greenroofcorp Yeah, that would be my approach (and how I’ve built multi-tenant/SaaS applications in the past).

<devils-advocate> On the other hand, one DB per customer means they can all be upgraded (or not) as they/you want. </devils-advocate> ;-)

Yeah definitely agree with others. I don't see any point of having a DB for each of your customer. The only thing it would do is give you more work and be a source of problems.

For having a company_id column per table this isn't necessary either. You just need those where it makes sense. It would not add anything to have it on data that would never be accessed using this link or don't have any logical link anyway. For example if you store the employees salaries each month, you would have a link to the employee but not to the company as it doesn't sound relevant even if in a way those salaries belong to the company but don't make much sense without the related employee.

It would all depend on your needs. Microsoft have written an article on this topic : -

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-design-patterns-multi-tenancy-saas-applications

@CodeNathan Nice article; thanks for sharing.

Personally, I would go for a hybrid approach. Keep your customer specific data in unique databases, and common/application information in a single DB. That way you could implement updates easier, and have more safeguards regarding isolation of customer data.

While issues of isolation are not an issue for smaller companies, it can be a major selling point for larger companies or those that are in certain industries. Industries such as banking, for instance, have regulatory requirements concerning the handling of customer data.

@martinbean How about the primary-keys of tables, for example, is it okay to use auto-increment? If one company makes 1000 transactions per day and we have multiple companies, will that be a problem?

I think multiple companies in one database is opening you up for severe problems down the road I'd stick with one company one database.

@greenroofcorp The only way an auto-incrementing primary key will bite you is if you use it in public endpoints, i.e. an API, and someone can then use it to download your database.

I know a location-based start-up who had a competitor drive them out of business because they were able to scrape their inventory via their public API. It’s therefore better to use either slugs or something like UUIDs.

Honestly sounds like op needs to sit down with a database specific dev.

Not always, but most of the "teams" I've been a part of at least had a sever consultant.

This allows the person to fully understand your app and situation and devise a good solution that fits your needs.

Sounds like your planning well, but maybe the cart is in front of the horse. Keeping it simple with a plan to scale might be appropriate but if you have companies promising to sign up then you may need more complexity right away.

I say that as I've seen a lot of ideas that people assume are the next Facebook (for app size example) but fail to realize it took Facebook years (about 8) to be worth anything. Spend a lot of resources developing this amazing scalable app and for nothing. As technology like aws, becomes available scaling really is a matter of a few clicks. From vertical and horizontal, so one large, replicated database using proper caching might be faster then multiple dealing with connections and updating tables etc.

But if you plan correctly you probably will never touch the db again expect for when you may need to add features that add more tables.

Orical, mssql, MySQL enterprise among others where designed for large data (big data). Where some db's like MySQL community (free version) do have some limitations, mainly memory issues.

I have a client that has a few million rows of data. No issues except generating reports. In that case we decided on lumen app to generate these reports in its own environment. To generate a report takes about 10 minutes. Sets the results to a reports table then compares the results on demand for profit loss statements, new customers, non-returning customers, revenue, sales, etc.

I think that multiple databases would be harder to maintain.

But, In certain scenarios, you may want different dbs.

I'd agree with most that one database is the best way. Then have a companies table and a foreign key in all the other tables to give ownership over the rest of the data stored in the database.

Then the best way to ensure a company gets the right data is creating a company middleware and wrap it around all the routes. Upon login it would set the current session company_id then you can validate against all requests to resources to ensure they have permission to access it.

You could also set in the parent controller a $company that is set through this method and passed around, so you have $company->{resource} ensuring all the returned data is linked to the company if you use Eloquent relationships between everything

I also agree with most, now my question is if you have single database for multiple companies how you protect data while editing or getting from other companies, if we use just primary key to edit any company can access data of other company,

please share some good ideas to protect data.

I make all my models descend from a ScopedModel that has a global scope, that appends a where('client_id', Auth::id()) to all the queries.

@umefarooq

How do you protect user A from updating user B, or how do you verify a user has permission to access a certain area?

Essentially the same thing. A user has a role and or permission to access a company's data. That can easily be check before the route is processed by middleware. Very similar to auth middleware. Same with any form submitting, either check before processing data or form request. Technically that's two of the same checks if you protect the route and after submitting.

Now I understand acls take a bit to grasp and it took me awhile. But once you do the stuff is super easy.

how you protect data while editing or getting from other companies

@umefarooq By authorizing requests: https://laravel.com/docs/master/authorization

You’d created policy classes that check if the current user can perform an action on the current entity.

Of course we have not heard from the all-wise --immck yet, as he would have the perfect answer for this.