-3

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?

timh
  • 1,572
  • 2
  • 15
  • 25
  • This sounds more complicated than it needs to be. White-labelling involves two things: (1) ensuring each page is specific to a certain customer, and (2) ensuring each page is rendered with the appropriate skin for that customer. For the first case, if the product already deals with an entity called "customer" then the customer in this context is a "super-customer". – halfer Nov 25 '14 at 10:15
  • Ah yes, the thing I refer to as a "customer" you are calling an "entity", and that is indeed the correct way to do it. In general the `1` would come from the session, or perhaps a lookup on the domain/URL. If you are looking for a shortcut, you could create a database module that adds the `entity` clause on for all queries that should be affected in this way. – halfer Nov 25 '14 at 10:18
  • @halfer - ok yes.. so you're saying a "database module" is the better way to approach than a "view"? And yes, the actually entityID would either have to come from php., either parsing the domain/url, or the api token used. Most likely I would base it on API token, because we also have an IOS app that will be calling this. – timh Nov 25 '14 at 10:25
  • @halfer - a google search for "mysql database module" results in a lot of general stuff... what exactly am i looking for, can you provide a link to a mysql doc or tutorial? – timh Nov 25 '14 at 10:28
  • Well no, that search won't net anything. This thing probably doesn't have a name, and it is in any case specific enough that you will have to write it yourself. The module I suggest would need to be called from each of your 100+ queries anyway - so all it would do is to simplify the binding of the relevant entity parameter. And I suspect your queries will need modification (in many cases it is not possible to just tack on an extra `WHERE` clause automatically, since that will often result in invalid SQL). – halfer Nov 25 '14 at 11:27
  • What database library are you using? PDO? – halfer Nov 25 '14 at 11:27
  • @halfer - so basically brute-force method then... modify each query. you're right, blindly tacking on the additional AND param will probably work for 90% but will fail for some. Im using MDB2. Ahh ok so you think maybe put some logic to modify using the MDB2 library api? ... – timh Nov 25 '14 at 16:59
  • Yes, I wouldn't call it brute-forcing though. You have a new object in your system (an `entity`) and you need to take account of it wherever that should be taken into account. If you have unit tests, then create a new branch, change your table build script, modify your queries as appropriate, and then run your tests against a single test entity until they all pass. – halfer Nov 25 '14 at 17:19
  • The module I suggested would bind an `entity_id` parameter where it is appropriate to do so. Don't know MDB2, does that support parameterisation (i.e. param binding)? – halfer Nov 25 '14 at 17:19
  • @halfer - yes, param binding. and i have a wrapper class around it. so i supposed i could just use a keyword in the sql like " AND entityID=__$ENTITYID$__" and have my wrapper class replace it when necessary. But why dont you think using views would be better?... since all my queries are already running off views,... and those views are already filtering out "deleted" rows. – timh Nov 25 '14 at 18:07
  • 1
    @halfer- thank for all your input! – timh Nov 25 '14 at 18:14
  • How would you inject the entity ID into the view? If you don't wish to inject it, and instead plan to hardwire it per entity, you'll start having a lot of duplicate views. Put another way, if you have 100 views at the moment, and 5 white-label entities, that would be 500 views - 400 of which are dups. – halfer Nov 25 '14 at 18:24
  • I wouldn't use `__$ENTITYID$__` (i.e. home-made binding) - use proper binding if you can. That gets you the benefit of the database engine's SQL cache, as well as being safer! – halfer Nov 25 '14 at 18:25

1 Answers1

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.

timh
  • 1,572
  • 2
  • 15
  • 25