Multi-Database Support for Separate Customers in the Same Project

[teletee]

Description:
I suggest introducing a feature in PHPMaker that allows the ability to add multiple databases with the same structure within a single project file. This feature would enable users to manage different customers with separate databases but under the same project setup.

Functionality Overview:

Multi-Database Configuration: The system should allow administrators to configure multiple databases, each representing a separate customer, within the same project. Each database would have the same schema but would be independent, allowing isolated data management per customer.

Customer-Specific Database Selection: Upon login, customers should be able to select or input their account name, which will correspond to a specific database. The account name could serve as the database name or label, ensuring that the correct database is used for the session.

Centralized Management: This feature will allow developers and administrators to maintain one set of application files while connecting to different databases based on the customer login. This eliminates the need to duplicate project files for each customer, improving efficiency and reducing the risk of inconsistencies.

Benefits:

Simplified Maintenance: With this feature, developers can maintain a single project file while supporting multiple customers, each with their own database. This significantly reduces the time and effort needed for updates and maintenance.
Customer Data Isolation: Each customer’s data will be stored in a separate database, ensuring data isolation and security while using the same application logic.
Scalability: This setup will make it easier to scale the application as the number of customers grows, as new databases can be added without modifying the core application.
Implementation Consideration:

Database Connection Handling: The system should dynamically switch the database connection based on the customer’s login credentials, ensuring the correct database is accessed.
User Interface: The login interface should be modified to include an additional field where the customer can input their account name or select from a predefined list.

[yinsw]

This is a feature I'm looking forward to have. Centralized Users/Permission management, and multiple project accessing different database.

[stefano]

+1

[sangnandar]

I have a working setup for this, using the old PHPMaker v2018 (haven't had time to upgrade yet).
1) master db, as schemata template.
2) customer dbs.

When customer register, it will do:
1) create new subdomain (using server cPanel API)
2) create new db, using master db as template. Tables userlevel and userlevelpermission SHOULD be copied with data (not structure only)
3) insert the new customer<->domain<->db relation into a table somewhere else

When customer login, it will do:
1) get the url they use, get dbname
2) Database_Connecting() server-event will connect to that dbname

Below is the code for db switching

function Database_Connecting(&$info) {
  if ($info["id"] === "DB") {
    if (isset($_SESSION["CUST_DB"])) {
      $info["db"] = $_SESSION["CUST_DB"];
    } else {
      // lookup to table `mycustomers` that holds domain<->dbname relation
      $servername = "";
      $username = "";
      $password = "";
      $database = "";

      $conn = new mysqli($servername, $username, $password, $database);
      if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
      }

      $host = preg_replace('/www\./i', '', $_SERVER["HTTP_HOST"]);

      $sql = "
        select 
          blah...blah..blah..,
          dbname
        from mycustomers
        where
          domain = '" . $host . "' 
        ";
      $result = $conn->query($sql);
      if ($result) {
        if ($result->num_rows > 0) {
          $val = $result->fetch_assoc();
        }
      } else {
        echo "Error: " . $conn->error;
      }
      $conn->close();

      $_SESSION["CUST_DB"] = $val["dbname"];
      $info["db"] = $_SESSION["CUST_DB"];
    }
  }
}

[sticcino]

how are you proposing to segregate the files, images and any other types of uploads?, or ae you just putting all the files in a single location?

you would need some sort of tenant_id for each client that you would need to link to the users/documents etc, then append it to the folder path you are creating/accessing

[sangnandar]

Table mycustomers actually table user in another database.

My setup:

• database marketing -> for marketing-management (different app)
• marketing reps acquire new customer -> input to user table.
• customer identities; id, domain, dbname, company_name [for header text, manifest file (for PWA), etc], are fetched during Database_Connecting() above.
• the query to fetch customer identities only fired once for each php-session the customer log into, stored in $_SESSION[].
• for file uploads, the script for creating new subdomain (using server cPanel API) would also create new folder using subdomain as the name, under /upload folder.
• it's important to note that some customers would use their own top domain instead of subdomain of mydomain.com so domain field in table user should be FQDN (not only the subdomain part). So does the folder name under /upload folder.