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

1

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.

    
asked by anonymous 24.01.2017 / 13:36

3 answers

3

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

    
24.01.2017 / 14:27
3

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

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:

  • Separate Banks. Create a database on the server for each tenant.
  • Shared Bank, Separate Scheme. Create a single database in which each tenant has a "schema" and the tables replicated by tenant.
  • Shared Bank, Shared Scheme. Create a single database with just one "schema" and create a column in all tables for the tenant code.
  • 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.

        
    24.01.2017 / 15:00
    1

    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 .

        
    24.01.2017 / 15:34