Collections, Journals and meta

Databases
1

I have three ideas I’m kicking around in my head for a new database shell in PHP. The basic idea is to automate the process of form and report creation to a large degree as this is 80% of the workload of any database backended site. I’m not out to create something as specific as a CMS, but I do want to get some of the lower level work handled at a framework level.

In this thread I want to discuss database ramifications alone - not the implementation details PHP side. Specifically the ramifications of three of the features - collections, journals, and meta.

By meta I mean the question of meta information. It’s pretty clear I’m going to end up with tables for holding meta information about the database. I’ve even gotten far enough along to name some of them: tables, fields, forms, summaries, metaindex. To prevent duplication (and the risk of asyncing the system) I intend to follow the SQL data on the fields where possible. Advice on this would be appreciated.

Collections is a concept I’ve come up with for associating tables together as a group. In a collection there will exist one table that serves as an index that holds meta information for the rows on the other tables. Importantly, the primary key of all tables in the collection comes from the index - hence all rows on all tables in the collection have an index row on the index table. This makes the keys unique within the collection. Record id 15 will appear once on the index and once on the table it was created on and nowhere else on any other table in the collection. This has the side effect of making the collection joinable to the index without any key collisions, and the tables of the collection can be joined to each other without having to specify additional on clauses due to the unique keys. this is the point of collections. Again, thoughts?

Journaling is a way to replicate the ‘trash bin’ effect of OS’es without the framework programmer pulling their hair out. If a table is journaled then the driver code creates a journal table that is a mirror of the actual table. Whenever an update or delete is called for the record that is about to be replaced is moved to the journal table. The journal table has two extra fields - one to hold a timestamp and the other for the journal table’s true primary key which needs to be different from the source table’s primary key. Journal tables can potentially get huge, so some mechanism for their pruning would need to be provided. Thoughts?

2

Other developers at my company built a database framework which works really well, and has become a great asset for the developers. One set of classes is based on an abstact “Model” class, which represents a single record. A generator script creates two classes for each table in the database. The DBCore_Tablename class contains everything specific to the table, such as column and key definitions. It is considered “un-touchable”. The DB_Tablename class extends DBCore_Tablename, contains almost nothing, and is only generated the first time, when it doesn’t exist. That’s where we put all customizations. The DBCore class is regenerated every time the table changes.

Then there’s a DB_Collection class, which is basically an array. A query like DB_Tablename::fetch_where($where) returns a DB_Collection object, populated with a list of DB_Tablename objects.

The Model class, extended to DBCore and DB, provides methods like insert(), update() and delete();