This is by all means not exhaustive, but it is meant as a somewhat easy show case in Layman's terms.
The simplest way is depicted here, no matter what Software is used, there will always be a Development, Quality and Production Environment.
Now, given that it is desirable to have a different physical Machine for the Production, the next Step would look like this:
Based on complexity (also considering other attached Systems) it might be advisable to have each Environment on its own Machine
As things progress, so called Development Objects (Tables, Programs, etc) are moved (or transported) through the Landscape
Staring at the Development System, then into the Quality System and eventually Production.
Over time, both Development and Quality System may fill up with undesirable Data, but can be refreshed with "real" Data from the Production System in order to better facilitate Development and Quality Assurance outcomes.
An additional Option is a Client environment.
The Configuration and Programs are shared, but the Master Data is different for each Client. Possible scenarios for this could be a Franchising Business where Products are the same, but Customer Master Data varies.
Generally speaking, any Table that contains ISO (International Standard Organization) Data is a shared Table between Clients.
Examples are: Currencies, Exchange Rates, Countries, Unit of Measures [and Conversion Factors]
Thus every change of the System applies to the entire Franchise, but the Master Data is kept separate and untouched.
Another use for Clients is Testing. Testing is usually only needed because of Configuration or Program changes, not because of Master Data.
The same Principe would apply to User training. A Base Client holding all the relevant Master Data for the Training scenarios is setup.
Before each Class is held, the Base Client is Copied to a New Client where the Students can follow along and enter Transactions.
Because the Base Client holds all the Characteristics (such as Credit Limit per Customer, Material Stock on Hand, etc)
the Master Data doesn't have to be created from Scratch over and over again. A simple Copy will do the Job and the Teacher has reliable Data for his scripts.
The final Example shows a possible Landscape for a merger of two Environments into a new third one.
During the consolidation Process, both active Environments needed to be maintainable for any changes. May they be business, third Party or legal related. Thus the new (middle) Landscape is continuously updated with the Configuration and Program changes from each life Production System. Some of that can be automated, but there is also considerable manual effort in retrofitting.
This little Code below is all that is needed for MARIADB or MySQL to Delete existing Client Data and Copy it from a Base Client again.
Short and sweet.
Since the SQL Statements are ISO Standard, it would work elsewhere too.
I am sure the Code can be improved, but the intention here was for easy understanding and not ultra sophisticated technique.
Obviously the Code only applies within one Database Instance, otherwise a second Connection needs to be established and the Copy Routine modified.
<?php
function copy_client($from, $to, $dry_run)
{
$dbHost="127.0.0.1";
$dbUsername="xxxxxxxx";
$dbPassword="yyyyyyyy";
$dbName="DataBaseName";
$tables='Tables_in_'.$dbName;
$copy=0;
$skip=0;
$Client_Field_Name='Client';
$fields="";
$conn = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
if (!mysqli_connect_errno())
{
$sql = "SHOW TABLES where $tables like 'time%';";
$result=$conn->query($sql);
if ($result->num_rows > 0)
{
while($row = mysqli_fetch_assoc($result))
{
$sql = "DESCRIBE ".$row[$tables];
$result1=$conn->query($sql);
if ($result1->num_rows > 0)
{
$Client_Field_Present=0;
$fields="";
while($row2 = mysqli_fetch_assoc($result1))
{
if ($fields != '')
$fields.=", ";
$fields.=$row2['Field'];
if ($row2['Field'] == "$Client_Field_Name")
$Client_Field_Present=1;
}
if ($Client_Field_Present == 1)
{
//Delete existing Client Data
$sql = "DELETE FROM ".$row[$tables]." where $Client_Field_Name='$to';";
if ($dry_run != 1)
$conn->query($sql);
//Copy $from Client Data to $to Client Data
$sql = "INSERT INTO ".$row[$tables]." ($fields) SELECT CASE WHEN $Client_Field_Name ='$from' THEN '$to' END AS
$fields FROM ".$row[$tables]." where $Client_Field_Name='$from';";
if ($dry_run != 1)
$conn->query($sql);
echo "<br>".$sql."<br>";
$copy++;
}
else
{
$skip++;
}
}
else
{
echo "<br>No Tables to describe.";
}
}
}
else
{
echo "<br>No Tables to show!";
}
echo "<br>$copy Tables copied and $skip Tables skipped.";
}
else
{
echo "<br>".mysqli_connect_error();
}
}
copy_client('000','999',1);
?>