A Flat File Database for Websites
2020-01-21When I first started blogging, I remember trying to figure out how the CMS worked. Where was my text being stored? It seemed to magically appear on the screen, summoned up from some mysterious chamber of secrets, which I soon found out was The Database.
The Database had all sorts of arcane rules and acted more like a bank vault than the simple file structure that I was familiar with on my pc. When I started building websites I began to notice how The Database came with a lot of baggage. It was vulnerable to being compromised whenever new information was added, requiring all sorts of security, and the task of making backups was way too complicated.
For a website with only a small amount of text, using The Database seemed cumbersome. Then, I learned about flat file databases and the many CMS products that use them, but there was always a disclaimer - a flat file database is not 'relational', so therefore terribly limited. In fact, there seemed to be a dismissive attitude, as if the lowly flat file database was a pathetic pretender with no real usefulness.
As I was looking for a way to simplify website development, I decided to investigate further. From a college class in database design, I remembered how keys could be used for joining tables, and the importance of following Codd's rules and normal forms. Since the storage medium was not defined in Codd's rules, it seemed that flat files could be used. As a language, I chose PHP with its array handling features. If I missed a few of the rules,it was reassuring to learn that most commercial database products miss them, too.
To create a website database, I started with two kinds of table records - 'pages' and 'posts', but soon added more - 'comments', 'categories', 'galleries', 'plugins', etc. The records would be character-delimited text files, and a second set of text files would be used for 'joining' table records. The executable code would need to include a 'dictionary' that defined the table records, a definition of the delimiter characters, and a set of functions to perform read, write and query operations.
It turned out that the dictionary, the delimiter definitions, and the read, write and query functions, could all be contained in a single php file and included once for every page load.
The 'read' function: A record type and file name are passed into this function. The record type is used to create a null associative array for the record type. Then the file name is used to read a text file into a character string which is 'exploded' into a second array. Each element of the second array is assigned to a named key in the associative array, which is then returned from the function to be used by the calling program.
The 'write' function: An associative array and a file name are passed to this function. The associative array is 'imploded' into a character delimited string which is then written into the text file specified by the file name.
Each table-to-table relationship has an associated text file containing 2 fields - one for each primary table key. For example, when a post record is written to the database, the post-category file is updated to include a tuple for each category that is selected.
To query all the posts for a particular category, 3 data elements are sent to the query function - the name of the join table 'post-category' in this case), and the values of the first and second elements For all posts in a category, the query function would receive the record type, 'posts', the primary key of the category, and a null value for the post key, indicating that all posts should be selected. An array of selected posts is returned.
Since the join files all have the same format, the functions for managing them can be used by any pair of tables.
I have found that this flat file database system has made my web development process much faster and simpler. It's possible to keep versions of each website on a laptop computer for testing, then upload the entire website has a zip file to a server, all in one step. Each website database can be downloaded as a backup just as easily. One of the best parts, however, is the ability to read the contents of the database in a text editor - extremely useful for debugging.
If you would like to see some examples of this flat file relational database in practice, you can check out the demos at https://lilaavenue.com/projects.