I'm developing an app for enterprise management. It will consume data from a MySQL database through a Web Server.
My question is: What strategy do you follow to store each customer's data?
Save all data from all clients in a single database and make the rules for selecting only authenticated client data; or do I create a separate database for each client?
If the option is to create a separate database: Is there a way to dynamically create the database?
Thank you in advance.
I do not know if there is a "best option".
Split database:
Pros
All stored in a single location.
You do not need to do "hints" to log into the bank.
Easy backup - single file
You do not have to worry about updating the structure of tables, fields, etc. in multiple banks.
Cons
Every query, you will be interested in doing one filter per client
Data open to other customers
Concern to always build quick queries, because some clients may have few records, others may have millions ...
Exponential growth of the base
Create one for each
Pros
Data from each "protected" client and its base
When the client wants to disconnect, sending the data will be faster
Database with size (MB) according to customer data
Custom base as needed
Querys without worrying about another filter
Cons
Time-consuming backup - nothing that can not be automated
Data loss can be a problem
Changes to DML can take a long time, since it has to do bank to bank.
Anyway, I think you have to study what you think is best for your application. Unfortunately in something you will lose, this will be almost inevitable, only choose the side.
I hope I have helped
In a cloud application the person / company that hires the system is called a tenant. It is important to give this name rather than "client" so as not to confuse it with the "client" entity that you normally have on your systems.
The basic idea is that you can somehow share the same features with multiple tenants thus reducing the effort of having to do a different installation for each tenant. This sharing has several levels: hardware, application, database, etc.
In the case of a database there are basically three ways you can share the database server:
By analyzing each scenario you will probably already understand the advantages and disadvantages of each scenario in question of maintenance and evolution of the system for each tenant.
In the MS website has a very complete article about this concept where you can learn more and decide on the best path.
You can create a database using PHP , for this you only have to run a database creation command, here's a simple example:
<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
$sql = 'CREATE DATABASE my_db';
if (mysql_query($sql, $link)) {
echo "Database my_db created successfully\n";
} else {
echo 'Error creating database: ' . mysql_error() . "\n";
}
?>
Important points in creating the database dynamically for each Client
In order to make use of this you would need to have a central database with the information of your clients and their respective databases, so you could associate each Client with their respective database.
In addition, it is advisable for each Client to have its own database connection user, with access only to the central base and to its database, as this would make your system safer and easier to trace from where came a change.
In the same way that the database was created, you could also create database users and give them permissions.
Follow a link explaining the User Creation and Permissions .