Flat File Relational Database

2019-10-01
(Note: The examples refer to The Nip Shoppe Catnip Emporium's business management software, which can be viewed HERE.)

By adding a method for joining tables, this flat-file database becomes relational. In this case, each necessary relationship is represented by an array that stores record keys as pairs.

All the functions needed to manage multiple tables and their relationships can be kept in a single PHP file, 'inc/database-functions.php' - in the 'inc' file , The table records and joining arrays are kept in the 'data' folder.

The database-functions file begins with 2 sections that are not actually functions (when you design your own database you can call things whatever you want!;), followed by a set of functions that perform operations on the database.

FIrst sectoin - delimiter character combinations are defined:

//Define delimiters
$dl1 = "XQX";
$dl2 = "XYX";


Second section - the record fields for each table are defined:

//Define tables
$dbtables = array (
"products" => array ("name", "price", "package", "status", "source", "description"),
"categories" => array (),
"customers" => array("username", "password", "lastname", "firstname", "company", "address", "city", "state", "zip", "email"),
"orderitems" => array ( "quantity", "date", "order-status", "fulfilled-date", "customer", "product", "paid")
);

The rest of the file contains all the functions necessary to work with the data - create record keys, read and write records, delete records, and manage the join arrays,.

I call the join arrays 'maps'. There is a map for each relationship between 2 tables - for instance the 'orderitem-customer' map and the 'product-category' map. Since all the maps have the same format - an entry for each relationship composed of a pair of fields, the same functions can be used with all of them.

All files in application:
 example of folders for flat file database application

Contents of data file:
 contents of data file for flat fle database

This is the 'category-product' map for the Nip Shoppe Catnip Emporium's database:

bulk-catnipXYXbackyard-favoriteXYX
XQXbulk-catnipXYXhelp-from-my-frendsXYX
XQXbulk-catnipXYXlazy-daysXYX
XQXbulk-catnipXYXpurple-hazeXYX
XQXbulk-catnipXYXstairway-to-heavenXYX
XQXcosmeticsXYXclaw-polishXYX
XQXcosmeticsXYXnippy-wisker-conditionerXYX
XQXcosmeticsXYXtail-spritzXYX
XQXculinaryXYXmagic-mouse-powderXYX
XQXculinaryXYXpickled-mouse-brainsXYX
XQXculinaryXYXspicy-chipmunk-seasoninXYX
XQXsupplementsXYXstart-me-upXYX

The first delimiter, "XQX" separates each entry and the second delimiter, "XYX", separates the fields for category and product.

When a customer orders a product, an order record is created that must be joined to both a product record and a customer record.

function addEntryToMap ($map, $key1, $key2) {
global $dl1, $dl2;
$mapstring = file_get_contents ($map);
$newentry = $key1 . $dl2 . $key2 . $dl2 . "n";
$mapstring = $mapstring . $dl1 . $newentry;
$mapstring = trim ($mapstring, $dl1);
$maparray = explode ($dl1, $mapstring);
array_unique ($maparray);
sort ($maparray);
$mapstring = implode ($dl1, $maparray);
file_put_contents ($map, $mapstring);
}


A typical query would contain the name of the join array, and one member of pair. For instance, to find all the orders for a customer, the orderitem-customer array would be selected and all pairs that matched the second term - the customer key - would be retrieved.


function selectMapEntries ($map, $key0, $key1) {
//returns an array with selected key
global $dl1, $dl2;
$selectedarray = array();
$mapstring = file_get_contents ($map);
$maparray = explode ($dl1, $mapstring);
foreach ($maparray as $id => $entrystring) {
$entryarray = explode ($dl2, $entrystring);
$mapkey0 = $mapkey1 = "";
if (array_key_exists (0, $entryarray)) {
$mapkey0 = $entryarray0) {
$mapkey1 = $entryarray1 {
if ($mapkey0 === $key0 ){
array_push ($selectedarray, $mapkey1);
}
}
else if ($key0 === "" && $key1!== "") {
if ($mapkey1 === $key1) {
array_push ($selectedarray, $mapkey0);
}
}
}
return $selectedarray;
}

Leave a comment