1

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.

5
  • 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, 2017 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, 2017 at 9:30
  • 35? OK, I was afraid it was hundreds -- split by day or customer.
    – Rick James
    Feb 20, 2017 at 16:36
  • Not sure if I understand. Split by customer as in, every table gets a customer_id FK ?
    – omrakhur
    Feb 21, 2017 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, 2017 at 16:44

1 Answer 1

1

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 ....

3
  • Could you please provide some more information or a link on the last part of your answer?
    – omrakhur
    Feb 17, 2017 at 15:03
  • What kind of "more information"? It's a standard GRANT statement, described in the manual.
    – mustaccio
    Feb 17, 2017 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, 2017 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.