MySQL supporting data used by different companies

[George Chilumbu]

I would like some ideas on this:

We have this Application that we use, and also share with our clients and partners around the world that we deal with. We have several different companies that supply us with different hardware components for our product, that we also customize to supply to our different customers. So we share this Application with our suppliers and also our customers. 

What we want is a database(s) that we can use for our Application using MySQL, and assure our customer (some competitors) and suppliers (also competitors) that their data is safe and not shared with other companies that use our Application as well. So basically, each supplier and customer should only see data that is relevant to them e.g., the product they supply and maybe some common data that we want all our partners (suppliers and customers) to see e.g., if our customer, say customer_a wants to change something about their customized product, us being the manufactures of the whole product should be able to note this change request (once we and customer_a agree) in our system so that all other companies involved (different suppliers) can get the change request all at once and confirm via the system. 

I would like ideas of how we can design this MySQL database with the requirements provided above, and be able to assure our partners that their data is safe and not shared unless that that needs to be shared like a change request among other examples.

[matthe...@percona.com]

Hi George,

This sounds like a typical SaaS style application. You would need to design your code/application so that each of your clients and partners has a separate database (ie: schema). Each schema would contain the same tables that the application reads. The difference is that when customer_a logs in, the code only reads the tables in schema_a. Any tables common to all clients/partners can be created in another schema to which the code also has access.

Cheers,

Matthew