Split the database to multiple clients or create one for each?

Question

I am developing an app for business management. It will consume data from a Mysql database through a Web Server.

My question is: What strategy to follow to store each customer’s data?

Save all data of all clients in a single database and make the rules to select only authenticated client data; or create a separate database for each client?

If the option is to create a separate bank: There is a way to dynamically create the database?

Thanks in advance.

Answer 1

Choose a suitable "Multi-tenant" or "Multitenancy" model

In a cloud application the person/company who hires use of the system is called tenant or "tenant". It is important to give this name instead of "client" so as not to confuse with the entity "client" that normally has in the systems.

The basic idea is that you can somehow share the same resources with multiple tenants thus reducing the effort of having to make a separate installation for each tenant. This sharing has several levels: hardware, application, database, etc.

In the case of database there are basically three ways you share the database server:

1. Separate Databases. Create a database on the server for each tenant.
2. Shared Database, Separate Schema. Create a single database in which each tenant has a "schema" and replicated tables per tenant.
3. Shared Bank, Shared Scheme. Create a single database with just a "schema" and create a column in all tables for tenant code.

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

Answer 2

I don’t know if there’s a "better option".

Split database:

Pros

All stored in one place.

No need to do "experiments" to log into the bank.

Easy backup - single file

No need to worry about updating the structure of tables, fields, etc in various banks.

Cons

Every query will take care to make a filter per client

Data open to other customers

Concern to always build quick querys, because some customers may have few records, others, may have millions...

Exponential growth of the base

---

Create one for each

Pros

Data of each client "protected" and in its base

When the customer wants to disconnect, sending the data will be faster

Database with size (MB) according to customer data

Customized base according to his need

Querys with no need to worry about one more filter

Cons

Time-consuming backup - nothing that can’t be automated

Data loss can be a problem

DML changes can take time, as you have to do bank by bank.

Anyway, I think you need to study what you think is best for your application. Unfortunately on something you will lose, this will be almost inevitable, just choose the side.

I hope I’ve helped

Answer 3

It is possible to create a database through the PHP, for this you just run a database creation command, follow 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

To make use of this you would need to have a central database with the information of your customers and their respective databases, so you could associate each Customer with their respective database.

In addition, it is advisable that each Customer has its own user connection to the database, with access only to the central database and its database, as this would make your system safer and easier to track where a change came from.

In the same way that the database was created, you could also create database users and give permissions to them.

Here is a link explaining the commands of user creation and permissions.