How to use MySQL queries to separate data, prepping for a multi-tenant / white-labelled web app?

Question

I've created a working web app using AngularJS over a rest API (Slim/PHP/MySQL) for my own company. Now I have a bunch of requests to white-label it for other companies like mine.

My backend probably has a hundred or so Rest API endpoints built in PHP that pull from MySQL.

What is the easiest/best way for me to then separate out all that data?

The design I'm leaning toward is to host all of this in the same database, and add an entityID column to every table, and assign an entityID to each of my white-label companies.

Then add this to each SQL command:

...AND entityID=X
ie.
SELECT * FROM invoices WHERE status='paid' AND entityID=1;

This seems like a very "brute-force" way to do it, because I would have to go edit 100+ SQL command, some of which are complex joins. Can you think of a better way? I was wondering if there would be some way to use "MySQL Views". I'm already using Views to hide "deleted rows":

CREATE VIEW invoices AS 
    SELECT * FROM _invoices where deleted is null;

So theoretically I should be able to just edit all my views... right?

CREATE VIEW invoices AS 
     SELECT * FROM _invoices where deleted is null 
          AND entityID=@entityID;
// BUT HOW would i set that entityID as a sql Variable from each PHP call?... 
//  I suppose I would have to add this before each call?
SET @entity=X;  

Can you think of another way, or help me implement that variable in a view, or point me in the right direction?

Answer 1

This is the method I went with:

• I kept the main database with all of the data of all the entity/companies, with having an entityID column.
• I created a new databases for each entity, but that database is filled with mysql views that reference back to the main database. For example:

.

 # ie. main database has tables: users, and invoices tables
 # if you i have a new company named "acme", then:
 create database acme;
 create view acme.users as select * from maindb.users where entityID=2;
 create view acme.invoices as select * from maindb.invoices where entityID=2;

In my situation, this allowed me not have to change any of my previous queries in my code. All I had to do was select the proper database, and all the data was automatically separate.

But keeping the actual data all in the same database, this simplified maintenance.