I consider this blog as an inspiration for others, not just as a promotion of QetriX ;-) so let me write a post about one of my former ideas for 3rd generation of my CMS (year 2007).
I was on a quest for automated database layer, so I came up with automated table joining without usage of “advanced” database features (designed for MyISAM engine).
From my former job I adopted a system of three-letter prefix for column names according to table name. The motivation for this was to have unique column names throughout the whole database schema. If I have table “users” (I use plural for table names), the prefix would be “usr”, so primary key would be “usr_id”. For PK in table “companies” it would be “com_id” and so on. If the table had similar name and the prefix was already taken, I’d use the next letter in order, so for “computers” along with “companies” it would be “com” and “cop” respectively.
For foreign keys I used even more specific approach, I used both table and prefix names from foreign table in the column name (plus “fk” suffix), so if user had relation to company, the column name in table “users” would be: “usr_companies_com_fk”.
Thanks to this I was able to specify $db->getDataFrom("users") and it would return data from all joined tables as well. For this purpose I had cached database schema (like “information_schema.tables” in MySQL).
And that’s it :)
Fun fact: The system was so automated, that when you deleted a row in one table, all referenced rows in all foreign tables has been deleted as well (incl. domains, users, locations etc). facepalm