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?