First timer here so please bear with me. I'm not a database expert so I'm here to learn.
My client asked me to build an online CRM that is an online payroll and rostering system where they can manage projects and employees. I'm almost done with developing the platform but I need to solve a problem. The database has 115 tables. Now to my understanding, it should be one database per CRM user to avoid conflicts. Am I right?
I'd like to know how I should handle this on the production side. On the development side it's pretty simple one database to test but on the production server they can't all possibly use one database? If there are 1000 companies using the CRM, does it mean 1000 different databases? I need help understanding how to handle this situation.
Best Answer
From a database design perspective, it's possible to design a database where an "unlimited" number of different organizations could share the same database, but would not be able to access any data except their own.
However, that's a step that one would need to take very early on in the process.
NOTE: If the individual companies require access to the database itself for direct querying, then separate databases for each company are the best practical answer. It would be difficult (if not impossible) to allow true ad hoc querying of the underlying data while preventing Company A from viewing any other company's data in the database.
To implement a multi-organization database, you'd start with what we'll call the [Organization] table. This table would uniquely identify every organization using the database. Let's call the primary key on this table Org_ID.
All other database tables (except very basic tables that would hold values common to all organizations) would include an Org_ID column, and all queries would reference the column (in fact, if possible, I would make this column a part of each table's primary key). Users who could log into the application would be tied to one (and only one) organization, and would only be able to access the data for that organization. NOTE: in practice, few if any tables are likely to be common to all organizations. Even tables like US state postal abbreviations might not truly be safe to apply to all organizations, as some might only want the "50 states plus District of Columbia", others might want to include abbreviations for US territories, and still others might want to include Canadian province abbreviations.
Since this would impact almost every table in the database, and every query in the application, it's unlikely to be a practical change now, if you're "almost done with developing the platform."
If the organizations using the application are fairly large, or the server involved is not terribly large, then the solution may simply be to spin up additional servers as you get additional organizations, with (say) 1-10 organizations per server.
Background: I was peripherally involved in work on a time entry system much like you've described, that was implemented as a multi-organization system much like I've described.
Limiting to one organization per database is more secure, as it avoids the possibility of a bug in the application allowing one organization to see another organization's data (especially if any of the organizations in question are competitors; getting access to a competitor's customer data would be a very bad glitch, and would possibly result in lawsuits), and allows each organization to access their database directly for ad hoc querying or reporting.
A final note: I'm providing this from a general database design perspective. I haven't used MySQL in particular enough to know (for example) how many separate databases a server can support, either due to explicit limits on MySQL itself, or to practical limits.