I have made an application which has a companies table at the moment. It would work fine for a few clients (~100 companies, according to my scaling calculations). The application is a simple CRUD app accessed on the web using REST protocols. Very few (if none) concurrent transactions take place at the same time.

At the moment the companies table is the most independent table of all. All others like employees, benefits, time_off, etc. are related to each other via the companies table.

Someone suggested that I could use MySql's schemas to isolate one company from another, and clients would see the same exact app. Essentially, the moment I sign up a new company, a new schema would fire up and create the same tables as it has for any other company. How does this work? Could I have multiple schemas on the same database? I'm working with AWS RDS so I don't wish to pay for multiple databases here as the total size of the data even after half a year's use has not exceeded half a GB.

  • Do you currently have less than a dozen "tables" in a single "database" (aka "schema")? And that is all in a single RDS instance? – Rick James Feb 18 '17 at 1:49
  • @RickJames they're all in a single RDS instance, yes. But there are 35 tables in all, mainly for small things, but I have kept data as decoupled as possible. – omrakhur Feb 20 '17 at 9:30
  • 35? OK, I was afraid it was hundreds -- split by day or customer. – Rick James Feb 20 '17 at 16:36
  • Not sure if I understand. Split by customer as in, every table gets a customer_id FK ? – omrakhur Feb 21 '17 at 9:10
  • For "isolation", consider one Database per customer. But, if you get to the point of having thousands of customers, there may be problems. – Rick James Feb 21 '17 at 16:44

In MySQL schema and database are synonymous: you use CREATE DATABASE or CREATE SCHEMA with equal effect. There's little physical isolation between schemas (databases), workload against one schema can potentially affect all schemas on the same server. However, there is logical access isolation: you can allow certain user(s) access to a specific schema only using GRANT ... ON schema.* TO ....

  • Could you please provide some more information or a link on the last part of your answer? – omrakhur Feb 17 '17 at 15:03
  • What kind of "more information"? It's a standard GRANT statement, described in the manual. – mustaccio Feb 17 '17 at 15:10
  • Splitting the companies across 100 databases (on the same RDS instance) will not cost much more disk space. It will be 100 directories, each with a small number of tables. Perhaps plus one database with information about the companies as a whole -- and accessible mostly to you, not them. – Rick James Feb 18 '17 at 1:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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